Example: http://pastebin.com/KL5VCxsr WITH t AS ( SELECT 1 c1, 100 C2, TO_DATE('24-MAY-13','dd-mon-rr') MYDATE FROM dual UNION ALL SELECT 2 c1, 200 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 2 c1, 201 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 3 c1, 300 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 4 c1, 400 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 6 c1, 600 C2, TO_DATE('24-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 1 c1, 150 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 2 c1, 250 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 3 c1, 350 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 4 c1, 450 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 5 c1, 550 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual UNION ALL SELECT 7 c1, 750 C2, TO_DATE('25-MAY-13','dd-mon-yy') MYDATE FROM dual ) SELECT mydate,c1_aux,c1,c2 FROM (SELECT level-1 c1_aux FROM dual CONNECT BY level<24) gen left join t PARTITION BY (mydate) ON c1=c1_aux ORDER BY mydate,c1_aux; On Fri, May 24, 2013 at 1:47 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote: > Hi > The example I posted was with test data. The real thing is that we have > data for each hour in a day (24 rows) but when Daylight Savings Time kicks > in we will have 25 rows because the hour 2 is accounted twice in that > specific day. > > So basically I have to deal with 0 to 24 rows (this is because some hours > possible has no data) in normal days and 0 to 25 rows in DST days, to write > a generic query to deal with this data I have thought of generate always 25 > rows no matter day and hour, even those hours with no data. > > The data can look like > > *DATE HOUR VALUE > ----------- ------ ------ > 24-MAR-2013 0 0 > 24-MAR-2013 1 100 > 24-MAR-2013 2 200 > 24-MAR-2013 3 300 > 24-MAR-2013 4 400 > 24-MAR-2013 5 500 > 24-MAR-2013 7 700 > 24-MAR-2013 9 900 > 24-MAR-2013 13 1300 > 24-MAR-2013 15 1500 > 24-MAR-2013 16 1600 > 24-MAR-2013 17 1700 > 24-MAR-2013 23 2300 > 30-MAR-2013 0 0 > 30-MAR-2013 1 100 > 30-MAR-2013 2 200 > 30-MAR-2013 2 200 > 30-MAR-2013 4 400 > 30-MAR-2013 5 500 > 30-MAR-2013 8 800 > 30-MAR-2013 11 1100 > 30-MAR-2013 14 1400 > 30-MAR-2013 15 1500 > 30-MAR-2013 16 1600 > 30-MAR-2013 20 2000 > 30-MAR-2013 23 2300* > > 30 of March was the DST day this year > > Thanks > > > -- > //www.freelists.org/webpage/oracle-l > > > -- Best regards, Sayan Malakshinov Senior performance tuning engineer PSBank http://orasql.org -- //www.freelists.org/webpage/oracle-l