Fill date gaps in SQL query with zeros

Question

I have the following structure (which came from some joins across tables,etc) which will be used to produce a chart.

Some IDs do not have data for all the dates, which result in some dashed lines in the chart.

The question is, how can I add the missing dates for each ID and fill their data cell with zeros?

As script:

(67, '2016-09-28 00:00:00.000',1),
(178, '2016-09-28 00:00:00.000',6),
(42, '2016-09-25 00:00:00.000',1),
(66, '2016-09-25 00:00:00.000',122),
(67, '2016-09-25 00:00:00.000',2),
(10, '2016-09-24 00:00:00.000',5),
(13, '2016-09-24 00:00:00.000',4),
(66, '2016-09-24 00:00:00.000',198),
(67, '2016-09-24 00:00:00.000',15),
(178, '2016-09-24 00:00:00.000',4),
(10, '2016-09-23 00:00:00.000',1),
(13, '2016-09-23 00:00:00.000',2),
(42, '2016-09-23 00:00:00.000',4),
(66, '2016-09-23 00:00:00.000',208),
(67, '2016-09-23 00:00:00.000',15)

Possible duplicate of [SQL: Daily report for with user counts](http://stackoverflow.com/questions/39849213/sql-daily-report-for-with-user-counts) — JohnHC, Oct 05 '16 at 10:57
@JohnHC I couldn't visualise the data structure so can't tell for sure if this is a duplicate — espressionist, Oct 05 '16 at 14:44

score 2 · Answer 1 · answered Oct 05 '16 at 10:55

Here is one method:

with t as (
      <your query here>
     )
select i.id, rt.roundedtime, coalesce(data, 0) as data
from (select distinct id from t) i cross join
     (select distinct roundedtime rt from t) rt left join
     t
     on t.id = i.id and t.roundedtime = rt.roundedtime;

In other words, create the list of dates and ids using a cross join. Then use a left join to bring in your data.

This query uses select distinct on your original data to get the lists of dates and ids. There may be more efficient ways to get each of these lists.

Thanks for the feedback. the problem is that not all dates are listed so they don't show up in your query — espressionist, Oct 05 '16 at 11:18

gofr1 · Accepted Answer · 2016-10-05T12:06:46.593

One more way with calendar table and CROSS JOIN:

;WITH YourQueryOutput AS (
--put your select statement here
), calendar AS (
SELECT  CAST(MIN(RoundedTime) as datetime) as d,
        MAX(RoundedTime) as e
FROM YourQueryOutput
UNION ALL
SELECT  DATEADD(day,1,d),
        e
FROM calendar
WHERE d < e
)

SELECT  t.ID,
        c.d,
        COALESCE(t1.[data],0) as [data]
FROM calendar c
CROSS JOIN (
    SELECT DISTINCT ID
    FROM YourQueryOutput
    ) t
LEFT JOIN YourQueryOutput t1
    ON t.ID = t1.ID and t1.RoundedTime = c.d
ORDER BY t.ID, c.d
OPTION(MAXRECURSION 0)

Output for sample you provided

ID  d                       data
10  2016-09-23 00:00:00.000 1
10  2016-09-24 00:00:00.000 5
10  2016-09-25 00:00:00.000 0
10  2016-09-26 00:00:00.000 0
10  2016-09-27 00:00:00.000 0
10  2016-09-28 00:00:00.000 0
...
178 2016-09-23 00:00:00.000 0
178 2016-09-24 00:00:00.000 4
178 2016-09-25 00:00:00.000 0
178 2016-09-26 00:00:00.000 0
178 2016-09-27 00:00:00.000 0
178 2016-09-28 00:00:00.000 6

score 0 · Answer 3 · answered Oct 05 '16 at 10:55

0

You can check ISNULL(YourDataColumn,0) in SELECT statement where you used join.

Example:

SELECT
    Q.QuestionId,
    Q.SenderType,
    ISNULL(Q.Data,0) AS Data
FROM @tblQuestion Q
LEFT JOIN @tblTeacher T ON Q.SenderId=T.Id AND Q.SENDERTYPE='TEACHER'
LEFT JOIN @tblInstitute I ON Q.SenderId=I.Id AND Q.SENDERTYPE='INSTITUTE'

IN above Select Statement Data column return 0 if no data available after join

answered Oct 05 '16 at 10:55

Sandip - Frontend Developer

14,397
4
35
61

As I understand there are some IDs which don't get in that query, and OP needs to show them with missing dates and zeros. – gofr1 Oct 05 '16 at 10:57
AS per i understand in output table no data in against some date so chart not render proper, so there should be some data if chart accept int then 0 else other value as per data type – Sandip - Frontend Developer Oct 05 '16 at 10:59
@gofr1: That's right, there are gaps in both IDs and RoundedTime in that query, so an ISNULL will not do much here – espressionist Oct 05 '16 at 11:20

Fill date gaps in SQL query with zeros

3 Answers3