1

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 enter image description here

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.

enter image description here

Regrads, Elio Fernandes

2
  • Must be both dates (start and end) included? – McNets Dec 13 '17 at 16:55
  • Can you provide your DBMS? (Note that the sql tag means the question involves the SQL language in general; for MS SQL Server, you should select the sql-server tag). – RDFozz Dec 13 '17 at 18:01
1

This is a pretty "procedural" approach, as opposed to set-based / pure RDBMS, but it should work. Basically you create a "frequency table" which tracks how many times your source table has a row that contains "a date", for each date from min to max. Sample code built to follow yours (i.e. you've already created your myDates table and filled it with data).

CREATE TABLE #DateFrequency (theDate date, freq int);
--TRUNCATE TABLE #DateFrequency;

DECLARE @theDate date, @maxDate date;
SELECT @theDate = MIN(startDate), @maxDate = MAX(endDate)
    FROM myDates

WHILE (@theDate <= @maxDate)
BEGIN
    INSERT INTO #DateFrequency (theDate, freq)
    SELECT @theDate, COUNT(id)
    FROM myDates
    WHERE @theDate BETWEEN startDate AND endDate;

    SET @theDate = DATEADD(DAY, 1, @theDate);
END

SELECT * FROM #DateFrequency;
--DROP TABLE #DateFrequency;

Then do whatever you need to do with the data in #DateFrequency; make it a permanent table (instead of #temporary) if you need.

PS: read this blog post by Aaron Bertrand about handling date ranges; it's not directly aimed at your issue because you (correctly) used the Date type instead of DateTime, but it's still what I consider mandatory reading if you're dealing with this kind of data and these kinds of queries.

0
4

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] <= '2020-12-31' -- Put the end date here 
    )
SELECT [Date]
    ,count(*)
FROM dates d
JOIN @myDates m ON d.[Date] >= m.startDate
    AND d.[Date] <= m.endDate
GROUP BY [Date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!
5
  • 1
    Note that you can limit the generation of the date dimension, using the minimum start date and maximum end date instead of hard-coded values. otherwise, beat me to it by that much. Here's a SQLFiddle link to the version I was finishing up. – RDFozz Dec 13 '17 at 18:16
  • @RDFozz - yes, thanks for pointing that out – Scott Hodgin - Retired Dec 13 '17 at 18:17
  • This is a cool alternative! It's fun to find a really good use for a recursive CTE, because they're pretty niche and occasionally a bit scary to the unfamiliar. Nice one =) – NateJ Dec 14 '17 at 16:08
  • @NateJ - a little something to put in the old toolbag :) – Scott Hodgin - Retired Dec 14 '17 at 16:10
  • @ScottHodgin interesting note, the min. value for MAXRECURSION seems to be just a bit below 1500. This is directly related to the difference between your "start" date and "end" date -- DATEDIFF(DAY, '20170101', '20201231'). Specifically, that plus 1 (1461 in this case). For the curious! – NateJ Dec 14 '17 at 16:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.