Find dates common in different rows

Question

I have a table which has 3 columns:

task_name -- data type is varchar(50)
start_date -- data type is date
end_date -- data type is date

I want to list the dates which fall within the range (between start_date & end_date) with how many times they fall in descending order.

For example if the data is :

task_name	start_date	end_date
ABC	2024-12-01	2025-01-31
DEF	2025-01-15	2025-02-10
GHI	2025-01-31	2025-02-03

then I want the result this way:

date	count	(comment)
2025-01-31	3	only date with the 3 tasks running
2025-01-15	2	2025-01-15 to 2025-01-30 has ABC and DEF overlapping
2025-01-16	2
…	2
2025-01-29	2
2025-01-30	2
		no 2025-01-31 here as it is the only date with count 3
2025-02-01	2	2025-02-01 to 2025-02-03 has DEF and GHI overlapping
2025-02-02	2
2025-02-03	2
2024-12-01	1	all other dates between 2024-12-01 and 2025-01-14, and 2025-02-04 to 2025-02-10, have count = 1
2024-12-02	1
…	1
2025-01-14	1
2025-02-04	1
…	1
2025-02-10	1

How can I do that?

Answer 1

A solution in your case is a recursive CTE.

A CTE (Common Table Expression) is a kind of temporary view that you define at the beginning of the query with WITH.
A recursive CTE is a CTE that calls itself, and is used to build data iteratively or recursively.

You need this because SQL doesn't have a native RANGE(start, end) type. You have to generate the rows one by one.

With a recursive CTE, you tell SQL:
Start at start_date. Then, each time, add a day. Repeat until you get past end_date.

Then you use this to generate the dates in between, which you can then count.

Example:

-- Create CTE
WITH DateExpanded AS (
    SELECT 
        task_name,
        start_date AS work_date,
        end_date
    FROM tasks
    UNION ALL
    SELECT 
        task_name,
        DATEADD(DAY, 1, work_date),
        end_date
    FROM DateExpanded
    WHERE work_date < end_date
)
-- Now group by data and count occurrences
SELECT 
    work_date,
    COUNT(*) AS occurrences
FROM DateExpanded
GROUP BY work_date
ORDER BY occurrences DESC
-- This piece is important if the date range is big, to avoid the default limit (100 layers) 
-- Be careful with that: set the maxrecursion only for the interval of time you need (maxdate - mindate)
-- If the recursion is too large or loops infinitely, the server will crash.
-- NEVER SET IT AT 0!!!
OPTION (MAXRECURSION 365);

SQL Server CTE Docs