I have create a temporary table of 'tasks', called #CFTasks that looks like this:
ID STARTDATE ENDDATE
1712060019 06/12/2017 07/12/2017
1712060019 07/12/2017 07/12/2017
1712060019 08/12/2017 08/12/2017
1712060019 07/12/2017 08/12/2017
1712060019 07/12/2017 12/12/2017
1712060019 12/12/2017 13/12/2017
1712060019 14/12/2017 14/12/2017
1712060019 07/12/2017 14/12/2017
1712060019 14/12/2017 NULL
1712060019 07/12/2017 14/12/2017
1712060019 18/12/2017 21/12/2017
1712060019 18/12/2017 21/12/2017
1712060019 19/12/2017 21/12/2017
1712060019 02/11/2018 24/09/2018
1712060019 14/09/2018 24/09/2018
1712060019 13/10/2018 05/12/2018
1712060019 13/10/2018 05/12/2018
1712060019 12/11/2018 05/12/2018
1712060019 19/12/2018 07/12/2018
1712060019 13/12/2018 07/12/2018
For each month from the 1st December 2017 to the current month, I want to know how many of these records were active, by which I mean the [STARTDATE] is less than or equal to the first of the month and the [ENDDATE] is greater than the first of the month or the [ENDDATE] is NULL
i.e.
[DUEDATE] <= @StartOfMonth
AND ( [ENDDATE] >= @StartOfMonth
OR [ENDDATE] IS NULL
)
For the sample task table provided, I'd expect the output to look like:
Month ActiveTasks
01/12/2017 0
01/01/2018 1
01/02/2018 1
01/03/2018 1
01/04/2018 1
01/05/2018 1
01/06/2018 1
01/07/2018 1
01/08/2018 1
01/09/2018 1
01/10/2018 1
01/11/2018 3
01/12/2018 4
I've created a date table too, so that I can get a count even where there are no active tasks on a particular reporting date but I can't find a way to join the two tables in such a way that I get the desired result.
My date table is created like this:
DROP TABLE IF EXISTS #CalendarMonths
DECLARE @StartDate date
SELECT @StartDate = '20171201';
DECLARE @CutoffDate date = getdate()
;WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(MONTH, @StartDate, @CutoffDate)
),
m(m) AS
(
SELECT DATEADD(MONTH, n, @StartDate) FROM seq
)
SELECT m AS [Month]
INTO #CalendarMonths
FROM m
ORDER BY m
OPTION (MAXRECURSION 0);
I've tried joining the two tables in every way I can think of but I'm always stuck with the fact that I don't actually want to group the output by the [STARTDATE] or [ENDDATE] but rather by the reporting date and the fact that I want NULLs to be considered active from the start date onward.
The closest I've managed to get (meaning the query doesn't simply fail) is the below. The problem is I know I'm going down a blind alley and that this approach just won't work, no matter how much I tweak it but I just can't construct the problem in a way my brain can solve it in SQL. In Excel the same task would take me a couple of minutes but I'm working with over 600,000 rows and Excel just isn't the right tool for the job.
SELECT cm.[Month],
COUNT(cft.ID) AS TasksDue
FROM #CalendarMonths cm
LEFT JOIN #CFTasks cft ON cft.STARTDATE <= cm.[Month]
AND cft.ENDDATE > cm.[Month]
GROUP BY cm.[Month]
ORDER BY cm.[Month]