Show 0 value as result of query

[Big0range]

Howdy. I have a couple of queries I run, whereby I take the results and paste into a spreadsheet. The data returned by the query is broken down by hour. If there is no data for the hour, I would like to see a 0, but the query does not return anything at all. I then have to copy data to a separate spreadsheet, find missing hours, insert 0s manually, then paste. HOW can I get the results to list a 0 where needed as a function of the query??

(I hope this request was clear!)

 

[SQLDenis]

This coalesce(DayPart,0)
has come from your new days table if the column is id then it would be

d2.id

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[ESquared]

btw, sorry about the mis-help, I read the question incorrectly. (Going too fast, I was!)

 

[Big0range]

Well, I didn't want to bug you further, but I can't find where to make the change to d2.id.

A little help??

 

[SQLDenis]

Query beginning:
select h.id,d2.id as Date,coalesce(Hourpart,0) as Hour ,coalesce(Countpart,0) as Data
from (select datepart(dd,DateTime) as DayPart,datepart(hh,DateTime) as Hourpart,count(datepart(hh,DateTime)) as Countpart

and query end:
group by datepart(dd,DateTime),datepart(hh,DateTime)) d
right join hours h on d.Hourpart = h.id
right join days d2 on d.Daypart = d2.id
order by 2,1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Big0range]

Wierderer and wierderer. At least it's not so easy I feel like a total idiot for not figuring it out. Output is still wrong - here's the first 50 rows:

8 1 8 85
9 1 9 227
10 1 10 266
11 1 11 254
12 1 12 195
13 1 13 233
14 1 14 217
15 1 15 213
16 1 16 164
17 1 17 56
18 1 18 28
8 2 8 66
9 2 9 180
10 2 10 196
11 2 11 199
12 2 12 174
13 2 13 185
14 2 14 190
15 2 15 191
16 2 16 124
17 2 17 44
18 2 18 30
19 2 19 2
NULL 3 0 0
NULL 4 0 0
8 5 8 101
9 5 9 194
10 5 10 273
11 5 11 305
12 5 12 217
13 5 13 252
14 5 14 83
15 5 15 98
16 5 16 66
17 5 17 22
18 5 18 3
8 6 8 23
9 6 9 51
10 6 10 98
11 6 11 94
12 6 12 75
13 6 13 76
14 6 14 94
15 6 15 68
16 6 16 59
17 6 17 31
18 6 18 6
8 7 8 34
9 7 9 53
10 7 10 40

It lists two more pairs of nulls and one group of three nulls further down.

Whatcha think?

 

[SQLDenis]

The nulls are because you don't have the values 3 and 4 in the hour table and you have them in the day table (if I am reading this output correctly)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/