CODEWITHSUNDEEP
sqlsql-servert-sqlview
downstairs
downstairs

Reputation: 21

How to fill in empty date rows multiple times?

I am trying to fill in dates with empty data, so that my query returned has every date and does not skip any.

My application needs to count bookings for activities by date in a report, and I cannot have skipped dates in what is returned by my SQL

I am trying to use a date table (I have a table with every date from 1/1/2000 to 12/31/2030) to accomplish this by doing a RIGHT OUTER JOIN on this date table, which works when dealing with one set of activities. But I have multiple sets of activities, each needing their own full range of dates regardless if there were bookings on that date.

I also have a function (DateRange) I found that allows for this:

SELECT IndividualDate FROM DateRange('d', '11/01/2017', '11/10/2018')

Let me give an example of what I am getting and what I want to get:

BAD: Without empty date rows:

date | activity_id | bookings         
-----------------------------
1/2  | 1           | 5
1/4  | 1           | 4
1/3  | 2           | 6
1/4  | 2           | 2

GOOD: With empty date rows:

date | activity_id | bookings         
-----------------------------
1/2  | 1           | 5
1/3  | 1           | NULL
1/4  | 1           | 4
1/2  | 2           | NULL
1/3  | 2           | 6
1/4  | 2           | 2

I hope this makes sense. I get the whole point of joining to a table of just a list of dates OR using the DateRange table function. But neither get me the "GOOD" result above.

Upvotes: 0

Views: 54

Answers (2)

downstairs
downstairs

Reputation: 21

I figured it out:

    SELECT        TOP 100 PERCENT masterlist.dt, masterlist.activity_id, count(r_activity_sales_bymonth.bookings) AS totalbookings
FROM            (SELECT        c.activity_id, dateadd(d, b.incr, '2016-12-31') AS dt
                          FROM            (SELECT        TOP 365 incr = row_number() OVER (ORDER BY object_id, column_id), *
                          FROM            (SELECT        a.object_id, a.column_id
                                                    FROM            sys.all_columns a CROSS JOIN
                                                                              sys.all_columns b) AS a) AS b CROSS JOIN
                             (SELECT DISTINCT activity_id
                               FROM            r_activity_sales_bymonth) AS c) AS masterlist LEFT OUTER JOIN
r_activity_sales_bymonth ON masterlist.dt = r_activity_sales_bymonth.purchase_date AND masterlist.activity_id = r_activity_sales_bymonth.activity_id
GROUP BY masterlist.dt, masterlist.activity_id
ORDER BY masterlist.dt, masterlist.activity_id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Use a cross join to generate the rows and then left join to fill in the values:

select d.date, a.activity_id, t.bookings
from DateRange('d', ''2017-11-01',''2018-11-10') d cross join
     (select distinct activity_id from t) a left join
     t
     on t.date = d.date and t.activity_id = a.activity_id;

It is a bit hard to follow what your data is and what comes from the function. But the idea is the same, wherever the data comes from.

Upvotes: 1

Related Questions