Mark C. Stock wrote:
> "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
I can think of several ways to solve it including the use of analytics.
But the OP doesn't indicate any good reason why two queries won't
suffice.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Thu Mar 10 2005 - 10:49:49 CST
