Re: add dummy rows

From: Ls Cheng <exriscer_at_gmail.com>
Date: Fri, 24 May 2013 11:47:03 +0200
Message-ID: <CAJ2-Qb-HDZ2s+pEk83boOJeoZBPu-5o-2P274nOuS6SsEKq-sg_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Fri May 24 2013 - 11:47:03 CEST