Merging two SELECT queries with same date fields-CodePudding

I have a table of Tasks where I have records for a particular date. I want to have all dates in one month displayed with numbers of tasks per date. If on some date there were no record of a task it should be written 0.

I have results with duplicating records from the same date when there were tasks on a given day.

Table:

Date        Tasks
2021-08-01  0
2021-08-02  0
2021-08-03  0
2021-08-03  25
2021-08-04  0
2021-08-04  18
2021-08-05  0
2021-08-05  31
2021-08-06  0

SQL code I am using:

Declare @year int = 2021, @month int = 8;
WITH numbers 
as 
( 
    Select 1 as value 
    UNion ALL 
    Select value  1 from numbers 
    where value   1 <= Day(EOMONTH(datefromparts(@year, @month, 1)))
) 
SELECT datefromparts(@year, @month, numbers.value) AS 'Datum', 0 AS 'Tasks' FROM numbers 
UNION 
SELECT CONVERT(date, added_d) AS 'Datum', COUNT(*) AS 'Tasks' FROM Crm.Task 
WHERE YEAR(added_d) = '2021' AND MONTH(added_d) = '8' GROUP BY CONVERT(date, added_d)

How can I remove duplicates that I will have only one date record 21-08-03 with 25 tasks?

Thank you for your help

CodePudding user response：

You requires OUTER JOIN :

WITH numbers as ( 
    Select datefromparts(@year, @month, 1) as value 
    UNION ALL 
    Select DATEADD(DAY, 1, value) as value
    from numbers 
    where value < EOMONTH(value)
) 
select num.value, COUNT(tsk.added_d) AS Tasks
from numbers num left join
     Crm.Task tsk
     on CONVERT(date, tsk.added_d) = num.value
GROUP BY num.value;

CodePudding user response：

If you want all dates for one month, you can do:

with dates as (
      select datefromparts(@year, @month, 1) as dte
      union all
      select dateadd(day, 1, dte)
      from dates
      where dte < eomonth(dte)
     )

You can then incorporate this into the logic using an outer join or subquery:

with dates as (
      select datefromparts(@year, @month, 1) as dte
      union all
      select dateadd(day, 1, dte)
      from dates
      where dte < eomonth(dte)
     )
select d.dte, count(t.added_d)
from dates d left join
     Crm.Task t
     on convert(date, t.added_d) = d.dte
group by d.dte
order by d.dte;

You can easily extend the logic for the CTE for more than one month, by adjusting the where clause in the second clause.