Count the total occurences for each day in given dates I have this table and don't enough SQL to make a query to achieve the result shown in the image below.

CREATE TABLE myDates (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')


Look at all days between 'startDate' and 'endDate' and count the total of occurrences per day:

Expected result


I just added this image from an Excel File to illustrate better what I am trying to achieve. I am moving from excel to sql. Ignore weekends and 22/12 and 25/12.



Regrads,
Elio Fernandes
Here's a set-based approach that uses an Common Table Expression (CTE) to generate a quick Date Dimension table and then we use the CTE to group by date

The answer is the same as @nates

Declare @myDates TABLE  (
    id integer NOT NULL,
    startDate date NOT NULL,
    endDate date NOT NULL
);

insert into @myDates(id, startDate, endDate)
values
(1, '2017-12-26', '2017-12-29'),
(2, '2017-12-29', '2017-12-29'),
(3, '2017-12-14', '2017-12-29'),
(4, '2017-12-18', '2017-12-21'),
(5, '2017-12-26', '2017-12-29'),
(6, '2017-12-28', '2017-12-29'),
(7, '2017-12-26', '2017-12-29'),
(8, '2017-12-25', '2017-12-27')
;

WITH dates ([Date])
AS (
    SELECT convert(DATE, '2017-01-01') AS [Date] -- Put the start date here

    UNION ALL

    SELECT dateadd(day, 1, [Date])
    FROM dates
    WHERE [Date] &lt;= '2020-12-31' -- Put the end date here 
    )
SELECT [Date]
    ,count(*)
FROM dates d
JOIN @myDates m ON d.[Date] &gt;= m.startDate
    AND d.[Date] &lt;= m.endDate
GROUP BY [Date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!