Sql Server group by time interval join zero values

Question

I have a table of the following form, with a datetime column and an ID column (see my other question on SO here).

    Datum                     SomeID 
2017-01-01 07:44:57.840         1   
2017-01-02 07:45:10.093         2   
2017-01-02 07:45:21.557         3    
2017-01-03 09:07:21.253         2  
2017-01-05 09:07:42.680         1 
2017-01-06 09:07:49.007         5 

I aim to group the rows by time intervals, in this case not dynamically, but by days, and count the number of unique IDs per day. What I have tried based on another response on SO:

Declare @START datetime2;
Set @START = Cast('2016/05/03' as datetime2);

Declare @END datetime2;
Set @END = Cast('2016/06/03' as datetime2);

WITH CTE_Dates AS
(
    SELECT @START AS cte_date
    UNION ALL
    SELECT DATEPART(Year, DATEADD(DAY, 1, cte_date)) as theYear, DATEPART(MONTH, DATEADD(DAY, 1, cte_date)) as theMonth, DATEPART(DAY, DATEADD(DAY, 1, cte_date)) As theDay
    FROM CTE_Dates
    WHERE DATEADD(DAY, 1, cte_date) <= @END
)

SELECT
DATEPART(Year, passageTime) as theYear, DATEPART(MONTH, passageTime) as theMonth, DATEPART(DAY, passageTime) As theDay,
ISNULL(COUNT(Distinct batchNbr), 0) AS counted_batchNbr
FROM [prod].[m1218].[passage]
LEFT JOIN CTE_Dates ON DATEADD(dd, 0, DATEDIFF(dd, 0, passageTime)) = cte_date
WHERE passageTime between @START and @END
GROUP BY DATEPART(Year, passageTime), DATEPART(MONTH, passageTime), DATEPART(DAY, passageTime)
ORDER BY theYear, theMonth, theDay

Upon executing I get the following error: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. I think it is because I am trying to create a union between rows with 3 columns (theYear, theMonth, theDay) and a single datetime2 object, @Start.

How do I fix this (and maybe the bottom part?), so that when I execute the query I get the grouped results by day and for the missing days, there is a row with a zero count for that day?

Answer 1

If I correctly understand what you are trying to do (get a list of all days in an interval and then LEFT JOIN to a table - and, that way, also get the days with 0 rows in that table), the way I see the CTE looking like is:

WITH CTE_Dates AS
(
SELECT CAST(@START AS date) AS cte_date
UNION ALL
SELECT DATEADD(dd, 1, cte_date)
FROM CTE_Dates
WHERE DATEADD(dd, 1, cte_date) <= CAST(@END AS date))
)

This way you get distinct days as DATE datatype. The SELECT should become much easier:

SELECT cte_date, COUNT(Distinct batchNbr) AS counted_batchNbr
FROM CTE_Dates
LEFT JOIN [prod].[m1218].[passage] ON cte_date = CAST(passageTime as DATE) 
GROUP BY cte_date
ORDER BY cte_date

I'll try to replicate this to make sure it works as intended.

EDITED - LEFT JOIN order of tables

Answer 2

Try this query instead:

Declare @START date = '2017-01-01';
Declare @END date = '2017-01-31';


WITH CTE_Dates AS
(
    -- Anchor member: create initial values
    SELECT @START AS cte_date,
           DATEPART(Year, @START) as theYear, 
           DATEPART(MONTH, @START) as theMonth, 
           DATEPART(DAY, @START) As theDay

    UNION ALL

    -- Recursive part: get next day
    SELECT DATEADD(DAY, 1, cte_date) AS cte_date,
           DATEPART(Year, DATEADD(DAY, 1, cte_date)) as theYear, 
           DATEPART(MONTH, DATEADD(DAY, 1, cte_date)) as theMonth, 
           DATEPART(DAY, DATEADD(DAY, 1, cte_date)) As theDay
    FROM CTE_Dates
    WHERE cte_date <= '2017-01-31'
)
SELECT DATEPART(Year, cte_date) as theYear, 
       DATEPART(MONTH, cte_date) as theMonth, 
       DATEPART(DAY, cte_date) As theDay,
       ISNULL(COUNT(Distinct [SomeID]), 0) AS counted_batchNbr
FROM CTE_Dates
LEFT JOIN [passage] ON DATEADD(dd, 0, DATEDIFF(dd, 0, [Datum])) = cte_date
WHERE cte_date between @START and @END
GROUP BY DATEPART(Year, cte_date), DATEPART(MONTH, cte_date), DATEPART(DAY, cte_date)
ORDER BY theYear, theMonth, theDay

Demo here