CODEWITHSUNDEEP
sqlsql-servert-sql
espressionist
espressionist

Reputation: 75

Fill date gaps in SQL query with zeros

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?

enter image description here

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)

Upvotes: 1

Views: 2768

Answers (3)

gofr1
gofr1

Reputation: 15997

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

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14689

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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.

Upvotes: 2

Related Questions