T-SQL Union returns duplicated row (date)

Question

I was trying to solve the issue of returning sum for each date in a specific date range, no matter if data is present for a day or not.

I have found that the best way would be to use a table pre-populated with all dates, select date range and union it with my data.

For some reason couldn't get left join to work, but union looks like working almost perfectly. The only issue is that it returns duplicates for dates where data is present in my data table.

SELECT NULL AS Visitors
,D.DATE AS Day
FROM Database.support.dates D
WHERE D.DATE BETWEEN @Start_date
    AND @End_date

UNION

SELECT count(V.id) AS Visitors
,DATEADD(day, 0, DATEDIFF(day, 0, V.CreateTime)) AS Day
 FROM Database.Clients.Clients V
 WHERE V.CreateTime BETWEEN @Start_date
    AND @End_date
AND V.WADID = @WADID
AND (
    V.WAPID = @WAPID
    OR @WAPID IS NULL
    )
GROUP BY DATEADD(day, 0, DATEDIFF(day, 0, V.CreateTime))
ORDER BY Day DESC

Was researching it whole last day and still can't get it working :/

Just a fun fact: if you take the entire query and union it to itself, you will actually get deduplicated results. — SQL_M
Why did you not just ask for help on the left join? You have conflicting where conditions. — paparazzo

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2018-04-11T06:41:21

I think that left joining your calendar table to your current query actually was the right thing to do. That being said, you can remedy your current situation by simply aggregating on the day, e.g. using MAX. By default, NULL values for each day would be ignored, so long as there is a non null visitor count present:

WITH cte AS (
    SELECT NULL AS Visitors, D.DATE AS Day
    FROM Database.support.dates D
    WHERE D.DATE BETWEEN @Start_date AND @End_date

    UNION

    SELECT COUNT(V.id), DATEADD(day, 0, DATEDIFF(day, 0, V.CreateTime))
    FROM Database.Clients.Clients V
    WHERE V.CreateTime BETWEEN @Start_date AND @End_date AND
        V.WADID = @WADID AND (V.WAPID = @WAPID OR @WAPID IS NULL)
    GROUP BY DATEADD(day, 0, DATEDIFF(day, 0, V.CreateTime))
)

SELECT Day, MAX(Visitors) AS Visitors     -- filter off unwanted NULL values
FROM cte
GROUP BY Day
ORDER BY Day DESC;

T-SQL Union returns duplicated row (date)

1 Answers