Help with query on sparse timeseries data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Massimiliano Campagnoli - 26 Sep 2008 14:33 GMT

Good morning,

I have a table like (Date, Hour, Value) where Date can be any date,
Hour is always between 6 (6am) to 22 (10pm) and Value is an integer.

E.G.

Date Hour Value
23/2/2002 6 2
23/2/2002 7 3
23/2/2002 8 2
23/2/2002 12 5

24/2/2002 8 9
24/2/2002 9 1
24/2/2002 10 2
24/2/2002 11 2
....

please note that some hour can be missing inside the same date, for
instance hour 9,10,11,13,14,15,16,17,18,19,20,21,22 are missing for
date 23/2/2002 and hour 6 and 7 are missing for date 24/2/2002

I need a query to list for every date and hour the corresponding value
but including also any missing hour (for missing hour the default for
Value is 0).

E.G.

Date Hour Value
23/2/2002 6 2
23/2/2002 7 3
23/2/2002 8 2
23/2/2002 9 0
23/2/2002 10 0
23/2/2002 11 0
23/2/2002 12 5
23/2/2002 13 0
23/2/2002 14 0
23/2/2002 15 0
23/2/2002 16 0
23/2/2002 17 0
23/2/2002 18 0
23/2/2002 19 0
23/2/2002 20 0
23/2/2002 21 0
23/2/2002 22 0
24/2/2002 6 0
24/2/2002 7 0
24/2/2002 8 9
24/2/2002 9 1
24/2/2002 10 2
24/2/2002 11 2
....

How can I achieve this ?

Thanks.

Reply to this Message

esmith2112 - 26 Sep 2008 15:41 GMT

Use a recursive query to generate the series of target dates, and
another declared table to materialize the hour range. Use these two
table to perform a left join against your target table, substituting
zeroes for the nulls, where no data existed. See example below.

with alldates(cal_dt) as (
values date('2008-01-01')
union all
select cal_dt + 1 day
from alldates
where cal_dt < '2008-01-05'
),
allhours(hour) as (
values
6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
),

mydata(cal_dt, hour, val) as (
values
(date('2008-01-03'),7,3),
(date('2008-01-03'),8,9),
(date('2008-01-03'),20,9),
(date('2008-01-05'),8,15),
(date('2008-01-05'),12,3)
)

select alldates.cal_dt , allhours.hour, value(mydata.val,0)
from alldates
join allhours on 1=1
left join mydata on alldates.cal_dt = mydata.cal_dt and allhours.hour
= mydata.hour
order by 1,2

On Sep 26, 9:33 am, Massimiliano Campagnoli <m...@paoloastori.com>
wrote:

> Good morning,
>
[quoted text clipped - 54 lines]
>
> Thanks.

Reply to this Message

Massimiliano Campagnoli - 27 Sep 2008 15:22 GMT

> Use a recursive query to generate the series of target dates, and
> another declared table to materialize the hour range. Use these two
[quoted text clipped - 90 lines]
>
> > Thanks.

Thanks, I got it.

Reply to this Message