Re: row merge

From: Thierry Van der Auwera <Thierry.Van.der.Auwera_at_StrandAssociates.com>
Date: Thu, 11 Apr 2002 11:52:09 GMT
Message-ID: <JNet8.11599$Ze.2963@afrodite.telenet-ops.be>

Try This :

SELECT ww.week,

       MAX(substr(decode(us.shortname, 'Wn',ww.hours || '/' || ww.workweek,null),1,15) as "Wn"),

       MAX(substr(decode(us.shortname, 'Hm',ww.hours || '/' || ww.workweek,null),1,15) as "Hm")

   from v_workweek ww,

        tabc_users us
   where ww.userid = us.id

     and (ww.UserID = 127 or ww.UserID = 21609)
     and ww.week >=
To_Char(To_Date('20020301000000','YYYYMMDDHH24MISS'),'YYYY/IW')
     and ww.week <=

To_Char(To_Date('20020401112350','YYYYMMDDHH24MISS'),'YYYY/IW') GROUP BY ww.week
order by ww.week
;

Greetings,
Thierry.

"William Watson" <straeww_at_yahoo.ca> wrote in message news:bb72c4f7.0204110341.1ef8309e_at_posting.google.com...
> Hi,
>
> Mabey I being stupid but, Does anyone know how to have the following
> sql merge the week rows so that there is only one row per week?
>
> SQL> SELECT
> 2 ww.week,
> 3 substr(decode(us.shortname, 'Wn',ww.hours || '/' || ww.workweek
> ,null),1,15) as "Wn",
> 4 substr(decode(us.shortname, 'Hm',ww.hours || '/' || ww.workweek
> ,null),1,15) as "Hm"
> 5 from
> 6 v_workweek ww,
> 7 tabc_users us
> 8 where
> 9 ww.userid = us.id
> 10 and (ww.UserID = 127 or ww.UserID = 21609)
> 11 and ww.week >=
> To_Char(To_Date('20020301000000','YYYYMMDDHH24MISS'),'YYYY/IW')
> 12 and ww.week <=
> To_Char(To_Date('20020401112350','YYYYMMDDHH24MISS'),'YYYY/IW')
> 13 order by
> 14 ww.week;
>
> WEEK Wn Hm
> ------- --------------- ---------------
> 2002/09 48.9/40
> 2002/09 38.8/40
> 2002/10 44.7/40
> 2002/10 41.3/40
> 2002/11 56.7/40
> 2002/11 38.3/40
> 2002/12 44.6/40
> 2002/12 39.8/40
> 2002/13 24.7/32
> 2002/13 33/32
>
> Many Thanks
Received on Thu Apr 11 2002 - 06:52:09 CDT