Re: Display Data by Shift

"DA Morgan" <damorgan_at_x.washington.edu> wrote in message news:1110410203.514671_at_yasure...
> Gomer wrote:
>
>> 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
>
> SQL doesn't do AND/OR very well. Write a second query or create views.
>
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

wouldn't this be solvable a rollup query?

++ mcs Received on Wed Mar 09 2005 - 17:37:03 CST