For each date in range, get records with date in range-CodePudding

Let’s say I have a table Orders and each order has a CreatedDate and a DeliveredDate column.

How can I select for each date in a range a count that includes all records where the date is between CreatedDate and DeliveredDate? Let's say getting results for the range 2021-10-04 to 2021-10-09

If the Orders table looks like this

ID       CreatedOn      DeliveredOn       
1        2021-10-04     2021-10-04      
2        2021-10-06     2021-10-07      
3        2021-10-06     2021-10-08 
4        2021-10-07     2021-10-08      
5        2021-10-08     2021-10-09

The result should look like this

Date            ActiveCount
2021-10-04      1
2021-10-05      0
2021-10-06      2
2021-10-07      3
2021-10-08      3
2021-10-09      1

So order 1 only applies towards the count of 2021-10-04 but order 3 applies towards the count of 2021-10-06, 2021-10-07, and 2021-10-08 because that is the day it is delivered on.

I’m not great with SQL and I am having trouble wrapping my head around how to do this. If someone could point me in the right direction I would greatly appreciate it!

CodePudding user response：

Assuming you will generate the dates table in your procedure based off of the input parameter values, you just need a LEFT JOIN and a COUNT with GROUP BY:

DECLARE @Dates TABLE ([Date] DATE);
DECLARE @Orders TABLE (ID INT, CreatedOn DATE, DeliveredOn DATE);

INSERT @Dates ([Date])
VALUES ('2021-10-04'),('2021-10-05'),('2021-10-06'),
       ('2021-10-07'),('2021-10-08'),('2021-10-09');

INSERT @Orders (ID, CreatedOn, DeliveredOn)
VALUES (1, '2021-10-04', '2021-10-04'),
       (2, '2021-10-06', '2021-10-07'),
       (3, '2021-10-06', '2021-10-08'),
       (4, '2021-10-07', '2021-10-08'),
       (5, '2021-10-08', '2021-10-09');

SELECT d.[Date], COUNT(ID) AS ActiveCount
FROM @Dates d
LEFT JOIN @Orders o ON o.CreatedOn <= d.[Date] AND o.DeliveredOn >= d.[Date]
GROUP BY d.[Date];

Order of the tables is important here. You want to see all of the dates in the range so you start with that table. The LEFT JOIN will return all of the records from that table and bring in records from the Orders table where the JOIN condition matches. You'll get a separate record for each match so use COUNT with GROUP BY to get the total count by day.

CodePudding user response：

If you have a table containing all the dates, something along the lines of this should work:

SELECT d.[date] as [Date], COUNT(*) as ActiveCount
FROM dates d
WHERE d.[date] >= @startDate AND d.[date] <= @endDate
LEFT JOIN orders o ON o.CreatedDate <= d.[date] and o.DeliveredDate >= d.[date]
GROUP BY d.[date]
ORDER BY [Date]