Display Data by Shift

Greetings,

I want to know how many shipments go in and out during a particular shift.

Now, I have a field that shows the value 'CKIN' or 'CKOT' for check in our check out; and I have a date field that gives me the current date and time a shipment was checked in or out.

What I want is to sum the shipments per shift, per hour, by CKIN or CKOT. I'm currently using this query:

select to_char(ctrl_dt, 'MM/DD/YY') DAY , to_char(ctrl_dt, 'Day') WEEKDAY, count(*) TOTAL, act_typ_txt IN_OUT from vsl_act
where act_typ_txt in ('CKIN', 'CKOT')
and trunc(ctrl_dt) >= trunc(sysdate-6)
group by to_char(ctrl_dt, 'MM/DD/YY'), to_char(ctrl_dt, 'Day'), act_typ_txt

to look at a week's worth of data. This gives me the following output:

DAY WEEKDAY TOTAL IN_O
-------- --------- ---------- ----

03/01/05 Tuesday           69 CKIN
03/01/05 Tuesday           75 CKOT
03/02/05 Wednesday         71 CKIN
03/02/05 Wednesday         78 CKOT
03/03/05 Thursday          97 CKIN
03/03/05 Thursday          79 CKOT
03/04/05 Friday            84 CKIN
03/04/05 Friday            69 CKOT
03/05/05 Saturday          55 CKIN
03/05/05 Saturday          47 CKOT
03/06/05 Sunday            46 CKIN
03/06/05 Sunday            35 CKOT
03/07/05 Monday            54 CKIN
03/07/05 Monday            76 CKOT
03/08/05 Tuesday           76 CKIN
03/08/05 Tuesday           66 CKOT
03/09/05 Wednesday         47 CKIN
03/09/05 Wednesday         35 CKOT

Now, any suggestions as to how I can break it down further, so I can display CKIN and CKOT by shift and/or hour?

Many Thanks,
Brian Received on Wed Mar 09 2005 - 12:02:50 CST