I need to count the number of campaigns per day based on the start and end dates of the campaigns

Input Table:

Out needed (result):

How do I need to write the SQL command to get the above result? thanks all

CodePudding user response：

Your sample data doesn't seem to match your desired results, but I think what you're after is this:

DECLARE @Start date, @End date;

-- first, find the earliest and last date:

SELECT @Start = MIN([Start date]), @End = MAX([End date])
  FROM dbo.Campaigns;

-- now use a recursive CTE to build a date range, 
-- and count the number of campaigns that have a row
-- where the campaign was active on that date:

WITH d(d) AS 
(
  SELECT @Start 
  UNION ALL 
  SELECT DATEADD(DAY, 1, d) FROM d WHERE d < @End
)
SELECT 
  [Date] = d, 
  [Count unique campaigns] = COUNT(*) 
FROM d 
INNER JOIN dbo.Campaigns AS c
ON d.d >= c.[Start date] AND d.d <= c.[End date]
GROUP BY d.d OPTION (MAXRECURSION 32767);

Working example in this fiddle.

CodePudding user response：

In the following solutions we leverage string_split with combination with replicate to generate new records.

select   dt        as date
        ,count(*)  as Count_unique_campaigns

from      
(
select    *
         ,dateadd(day, row_number() over(partition by Campaign_name  order by (select null))-1, Start_date) as dt
from     (
         select   *
         from     t
         outer apply string_split(replicate(',',datediff(day, Start_date, End_date)),',')
         ) t
) t
group by dt
order by dt

For SQL in Azure and SQL Server 2022 we have a cleaner solution based on [ordinal][4].

"The enable_ordinal argument and ordinal output column are currently supported in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics (serverless SQL pool only). Beginning with SQL Server 2022 (16.x) Preview, the argument and output column are available in SQL Server."

select   dt        as date
        ,count(*)  as Count_unique_campaigns

from      
(
select    *
         ,dateadd(day, ordinal-1, Start_date) as dt
from     (
         select   *
         from     t
         outer apply string_split(replicate(',',datediff(day, Start_date, End_date)),',', 1)
         ) t
) t
group by dt
order by dt

Fiddle