Re: Can I do this with a view?

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/07/19
Message-ID: <31EFDD24.1948_at_teldta.com>#1/1

Tom Alborough wrote:
>
> Hi,
>
> I have Table Unit_Day_Shift:
>
> Unit_ID Day_Date Shift_SN Run_Hours
> =================================================
> 123 1-Jan 1 2
> 123 1-Jan 2 4
> 123 1-jan 3 0
> 345 1-jan 1 0
> 345 1-jan 2 2
> ..
> 123 2-jan ...
>
> I need Table Unit_Day:
>
> Unit_ID Day_Date Shift_1_Hours Shift_2_Hours Shift_3_Hours
> =====================================================================
> 123 1-Jan 2 4 0
> 345 1-Jan ...
>
> (I also have---
>
> Table Day:
>
> Day_Date
> ========
> 1-Jan
> 2-Jan
> 3-Jan
> ..
>
> and Table Shift:
>
> Shift_SN Start_Offset_Hours End_Offset_Hours
> ========================================================
> 1 0 .333
> 2 .333 .666
> 3 .666 1
>
> Is this doable with a view?
>
> Thanks,
> Tom

PROMPT> cat /tmp/test.sql
drop table Unit_Day_Shift;
create table Unit_Day_Shift
(unit_id number, day_date date, shift_sn number, run_hours number);

insert into  Unit_Day_Shift values(123,sysdate,1,2);
insert into  Unit_Day_Shift values(123,sysdate,2,4);
insert into  Unit_Day_Shift values(123,sysdate,3,0);
insert into  Unit_Day_Shift values(234,sysdate,1,6);
insert into  Unit_Day_Shift values(234,sysdate,2,4);
insert into  Unit_Day_Shift values(234,sysdate,2,5);
insert into  Unit_Day_Shift values(234,sysdate,3,2);

select * from unit_day_shift;

col aa format 999,990 heading 'SHIFT_SN_1'
col bb format 999,990 heading 'SHIFT_SN_2'
col cc format 999,990 heading 'SHIFT_SN_3'
col dd format a9      heading 'DAY_DATE'
select unit_id, trunc(day_date),
     sum(decode(shift_sn,1,run_hours,0)) aa,
     sum(decode(shift_sn,2,run_hours,0)) bb,
     sum(decode(shift_sn,3,run_hours,0)) cc

from unit_day_shift
group by unit_id, trunc(day_date)
order by unit_id, trunc(day_date);
exit;

PROMPT> sqlplus -s / _at_/tmp/test.sql

   UNIT_ID DAY_DATE SHIFT_SN RUN_HOURS

---------- --------- ---------- ----------
       123 19-JUL-96          1          2
       123 19-JUL-96          2          4
       123 19-JUL-96          3          0
       234 19-JUL-96          1          6
       234 19-JUL-96          2          4
       234 19-JUL-96          2          5
       234 19-JUL-96          3          2
 

   UNIT_ID TRUNC(DAY SHIFT_SN_1 SHIFT_SN_2 SHIFT_SN_3

---------- --------- ---------- ---------- ----------
       123 19-JUL-96          2          4          0
       234 19-JUL-96          6          9          2
 

If you have more shift_sn values add more "sum(decode())" statments. If shift_sn values are unknown, use sql(1) that generates sql(2) then execute the generated sql(2).

brian.maclean_at_teldta.com

"Everything should be made as simple as possible, but not simpler."

                       Albert Einstein

Received on Fri Jul 19 1996 - 00:00:00 CEST