# Thread: Order Counts by hour, with hours that have no data and need to show 0

1. Registered User
Join Date
Apr 2010
Posts
32

## Unanswered: Order Counts by hour, with hours that have no data and need to show 0

Hello,

I have a month's worth of data to count by hour. most hours have data, some do not. when i count them by hour, i might not get all 24 rows as no data for an hour means it will not have a row. I still need the row, and it should show 0.

sample data

Code:
```WITH tbl1 (dd1, hr1, value1) AS (
VALUES
('2015-12-01', '00', '1234')
, ('2015-12-01', '02', '2345')
) (
SELECT dd1, hr1, value1
FROM tbl1
) WITH UR```
this results in:

Code:
```2015-12-01	00	1234
2015-12-01	02	2345```
I need to fabricate hour "01" with "0" for the value. I came up with this.

Code:
```WITH tbl1 (dd1, hr1, value1) AS (
VALUES
('2015-12-01', '00', '1234')
, ('2015-12-01', '02', '2345')
), tbl2 (dd2, hr2, value2) AS (
VALUES
('2015-12-01', '00', '0')
, ('2015-12-01', '01', '0')
, ('2015-12-01', '02', '0')
) (
SELECT COALESCE(dd1, dd2), COALESCE(hr1, hr2), COALESCE(value1 , value2)
FROM tbl1
RIGHT OUTER JOIN tbl2 ON dd1=dd2 AND hr1=hr2
) WITH UR```
this results what i need:

Code:
```2015-12-01	00	1234
2015-12-01	02	2345
2015-12-01	01	0```
but it is not scalable because every column in the original data needs to show up in the temp table, enlarging it exponentially with every field that it's grouped by. this example is dumbed down so no aggregation is present but it's simpler for ease of understanding.

I can only do a select, but since this is going in excel, an excel solution to fill in the blanks would work as well. it actually needs to go in iSeries db but i can translate it later. I just need to get the logic down.

please advise what the best way to do this.. right in db2/iseries, or excel or anywhere else, and briefly what a method might look like?

thanks,
-don

2. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
I don't think that the code you posted is syntactically correct, but I don't have a DB2 machine handy to test it.

I concocted a "rude start" that might help you get going using a different SQL dialect, but it ought to either run "as is" or at least give you some ideas:
Code:
```WITH hours (h) AS (
SELECT h
FROM (VALUES ('00'), ('01'), ('02'), ('03'), ('04'), ('05')
,     ('06'), ('07'), ('08'), ('09'), ('10'), ('11')
,     ('12'), ('13'), ('14'), ('15'), ('16'), ('17')
,     ('18'), ('19'), ('20'), ('21'), ('22'), ('23')) AS z(h)
), tbl1 (dd1, hr1, value1) AS (
SELECT dd1, hr1, value1
FROM (VALUES
('2015-12-01', '00', '1234')
,        ('2015-12-01', '02', '2345')) AS z2(dd1, hr1, value1)
), tbl2 (dd2, hr2, value2) AS (
SELECT dd2, hr2, value2
FROM (VALUES
('2015-12-01', '00', '0')
,        ('2015-12-01', '01', '0')
,        ('2015-12-01', '02', '0')) AS z3(dd2, hr2, value2)
)
SELECT dd, h, v
FROM hours
FULL OUTER JOIN (SELECT * FROM tbl1
UNION ALL SELECT * FROM tbl2) AS z4 (dd, hr, v)
ON (hours.h = z4.hr)```
-PatP

3. Registered User
Join Date
May 2010
Location
India
Posts
90
Don,

There is no need for tbl2. See the following example.

Code:
```with tbl_h ( hr1 ) as
( select * from  (VALUES ('00'), ('01'), ('02'), ('03'), ('04'), ('05')
,     ('06'), ('07'), ('08'), ('09'), ('10'), ('11')
,     ('12'), ('13'), ('14'), ('15'), ('16'), ('17')
,     ('18'), ('19'), ('20'), ('21'), ('22'), ('23') ) ) ,

tbl1 (dd1, hr1, value1) AS
( select * from (   VALUES
('2015-12-01', '00', '1234') ,
('2015-12-01', '02', '2345') ))

select '2015-12-01' , -- date for which query is executed
tbl_h.hr1 , coalesce(tbl1.value1,'0')
from tbl_h
left outer join tbl1 on  tbl_h.hr1 = tbl1.hr1
order by tbl_h.hr1```
Satya

4. Registered User
Join Date
Jul 2013
Location
Moscow, Russia
Posts
666
Hello,

try this:
Code:
```WITH tbl1 (dd1, hr1, value1) AS (VALUES
('2015-12-01', '00', '1234')
, ('2015-12-01', '02', '2345')
, ('2015-12-03', '03', '3456')
)
, h (hr) as (
values 0
union all
select hr+1
from h
where hr<23
)
SELECT d.dd, digits(dec(h.hr, 2)) hr, coalesce(t.value1, 0) value1
FROM (
select distinct dd1
from tbl1
) d (dd)
cross join h
left join tbl1 t on t.dd1=d.dd and t.hr1=digits(dec(h.hr, 2))
order by d.dd, h.hr```
If you want to fill date gaps as well (for example, to have 24 rows with zeroes for 2015-12-02), this statement can be modified.

5. Registered User
Join Date
Apr 2010
Posts
32
thanks everyone for the replies!

the recursion one works for multiple dates. i see how it's done. i can add missing dates using this as well. i forgot to add this in my original data sample.

appreciate it!

-don

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•