Search code examples
sqlsql-serverdatejoincross-apply

SQL - How to return every day of month even with no data available

Below is my initial query and the data it returns:

SELECT
    User
    ,Client
    ,Date
    ,Total
FROM SampleTable 
WHERE Date BETWEEN '20200101' AND '20200131'

InititalQueryResult

Instead, I would like it to include a row for every day in the month like the table below:

IntendedQueryResult

I have a standard ANSI date table already; however, I've been unable to find a solution that includes dimensions that also need to be joined on (User/Client):

I don't want my table to look like this:

UnintendedQueryResult

I am thinking my answer lies somewhere in the cross apply/subquery realm, but am new to SQL, so I'm having trouble understanding exactly how that will be done. Any help would be appreciated. Thanks!

Solution

  • If you have all dates in the table, you can use that as the source. Then a cross join and left join:

    select uc.user, uc.client, d.date,
       coalesce(st.total, 0)
from (select distinct user, client
      from sampletable
     ) uc cross join
     (select distinct date
      from sampletable
     ) d left join
     sampletable st
     on st.user = uc.user and st.client = uc.client and
        st.date = d.date;

    If the base table doesn't have all the dates you can generate them in various ways:

    • Using a calendar table.
    • Using a recursive CTE.
    • Generating a bunch of numbers and constructing the dates.