CODEWITHSUNDEEP
sqlsql-server
Vadzim Savenok
Vadzim Savenok

Reputation: 940

SQL how to force to display row with 0 if no data available?

My table returns results as following (skips row if HourOfDay does not have data for particular ID)

ID      HourOfDay   Counts
--------------------------
1         5           5
1         13          10
1         23          3
..........................HourOfDay up till 23
2         9           1

and so on.

What I am trying to achieve is to force showing rows displaying 0 for HoursOfDay, which don't have data, like following:

ID      HourOfDay   Counts
--------------------------
1         0           0
1         1           0
1         2           0
1......................
1         5           5
1         6           0
1......................
1         23          3
2         0           0
2         1           0
etc.

I have researched around about it. It looks like I can achieve this result if I create an extra table and outer join it. So I have created table variable in SP (as a temp workaround)

DECLARE @Hours TABLE
(
    [Hour]                      INT                 NULL
);
INSERT INTO @Hours VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)
                          ,(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);

However, no matter how I join it, it does not achieve desired result.

How do I proceed? Do I add extra columns to join on? Completely different approach? Any hint in the right direction is appreciated!

Upvotes: 1

Views: 117

Answers (1)

SqlZim
SqlZim

Reputation: 38023

Using a derived table for the distinct Ids cross joined to @Hours, left joined to your table:

select 
    i.Id
  , h.Hour
  , coalesce(t.Counts,0) as Counts
from (select distinct Id from t) as i
  cross join @Hours as h
  left join t 
    on i.Id = t.Id
   and h.Hour = t.HourOfDay

rextester demo: http://rextester.com/XFZYX88502

returns: 

+----+------+--------+
| Id | Hour | Counts |
+----+------+--------+
|  1 |    0 |      0 |
|  1 |    1 |      0 |
|  1 |    2 |      0 |
|  1 |    3 |      0 |
|  1 |    4 |      0 |
|  1 |    5 |      5 |
|  1 |    6 |      0 |
|  1 |    7 |      0 |
|  1 |    8 |      0 |
|  1 |    9 |      0 |
|  1 |   10 |      0 |
|  1 |   11 |      0 |
|  1 |   12 |      0 |
|  1 |   13 |     10 |
|  1 |   14 |      0 |
|  1 |   15 |      0 |
|  1 |   16 |      0 |
|  1 |   17 |      0 |
|  1 |   18 |      0 |
|  1 |   19 |      0 |
|  1 |   20 |      0 |
|  1 |   21 |      0 |
|  1 |   22 |      0 |
|  1 |   23 |      3 |
|  2 |    0 |      0 |
|  2 |    1 |      0 |
|  2 |    2 |      0 |
|  2 |    3 |      0 |
|  2 |    4 |      0 |
|  2 |    5 |      0 |
|  2 |    6 |      0 |
|  2 |    7 |      0 |
|  2 |    8 |      0 |
|  2 |    9 |      1 |
|  2 |   10 |      0 |
|  2 |   11 |      0 |
|  2 |   12 |      0 |
|  2 |   13 |      0 |
|  2 |   14 |      0 |
|  2 |   15 |      0 |
|  2 |   16 |      0 |
|  2 |   17 |      0 |
|  2 |   18 |      0 |
|  2 |   19 |      0 |
|  2 |   20 |      0 |
|  2 |   21 |      0 |
|  2 |   22 |      0 |
|  2 |   23 |      0 |
+----+------+--------+

Upvotes: 4

Related Questions