Re: row merge

Hi,

Yep, I was being stupid. It works.

Many Thanks,
Bill

Thierry Van der Auwera wrote:

> 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 - 07:31:58 CDT