I have below table in MySQL 5.6
| Name | StartDate | EndDate |
|---|---|---|
| First | 2021-03-20 | 2021-03-21 |
| Second | 2021-03-20 | 2021-03-23 |
| Third | 2021-03-20 | 2021-03-20 |
I need a query in MYSQL to output the count of 'Name' which was alive on each day (between StartDate & EndDate) for a given date range. Is this possible via SQL?
Output: Say we give range as 2021-03-20 and 2021-03-23
| Date | Count |
|---|---|
| 2021-03-20 | 3 |
| 2021-03-21 | 2 |
| 2021-03-22 | 1 |
| 2021-03-23 | 1 |
One method is to start with a row for each day and then use a corrected subquery. Let me assume you have a calendar table:
select c.date,
(select count(*)
from t
where c.date between t.startdate and t.enddate
) as num_active
from calendar c
where c.date >= '2021-03-20' and c.date3 <= '2021-03-23';
If you don't have a calendar table, you can construct one on the fly for the query -- assuming you have a table with enough rows. For instance:
select c.date,
(select count(*)
from t
where c.date between t.startdate and t.enddate
) as num_active
from (select date( '2021-03-20' + interval (@rn := @rn + 1) day) as date
from t cross join
(select @rn := -1) params
) c
where c.date >= '2021-03-20' and c.date3 <= '2021-03-23';
You can really use any table for the c subquery -- so long as it has enough rows. If you know you want exactly four dates, you can hardcode those values:
select c.date,
(select count(*)
from t
where c.date between t.startdate and t.enddate
) as num_active
from (select date( '2021-03-20' ) as date union all
select date( '2021-03-21' ) as date union all
select date( '2021-03-22' ) as date union all
select date( '2021-03-23' ) as date
) c
where c.date >= '2021-03-20' and c.date3 <= '2021-03-23';
Assuming you are working with dates and not datetimes this should work. Basically what you are doing is creating a table of dates in the requested range and then for each of those dates you make a cartesian pair with dates of the data you have. In the end you simply count the amount of pairs bounded to each date.
--The data table
DECLARE @dataTable TABLE (Name nvarchar(100), StartDate date, EndDate date)
INSERT INTO @dataTable VALUES('First', '2021-03-20', '2021-03-21')
INSERT INTO @dataTable VALUES('Second', '2021-03-20', '2021-03-23')
INSERT INTO @dataTable VALUES('Third', '2021-03-20', '2021-03-20')
--end
--Create a temporary table with dates in range
DECLARE @dates TABLE(dt date)
DECLARE @dateFrom date
DECLARE @dateTo date
SET @dateFrom = '2021/03/01'
SET @dateTo = '2021/03/31'
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END
--end
--Do the thing
SELECT dt as Date, COUNT(*) as Count
FROM @dates
inner join @dataTable ON dt >= StartDate and dt <= EndDate
GROUP BY dt
--end
