How to get active totals by date

buzza1234 

I would have joined to calendar table instead, but didn’t have one.   So created in SQL.   I made some assumptions based on what I think you wanted as an output.  Let me know if that was what you needed.  

Create Table #Policy (PolNumber int, start_date date, end_date date);

Insert Into #Policy Values (100,'2023-01-04','2024-02-22')
Insert Into #Policy Values (101,'2022-01-01','2025-12-26')
Insert Into #Policy Values (102,'2024-02-01','2024-12-16')
Insert Into #Policy Values (104,'2023-06-24','2024-05-31');

DECLARE @startdate date = '20220101';
DECLARE @cutoffdate date = '20251231';

WITH
SEQ(n) AS
(SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)),

CAL_TBL AS
(SELECT CONVERT (date, DATEADD(DAY, n, @StartDate)) AS CAL_DATE FROM SEQ),

POLICY_PROP AS
(SELECT DISTINCT PolNumber FROM #Policy),

POLICY_STAT AS
(SELECT
PRP.PolNumber,
CAL.CAL_DATE,
PLY.END_DATE,
CASE WHEN CAL.CAL_DATE BETWEEN PLY.START_DATE AND PLY.END_DATE THEN 1 ELSE 0 END AS ACTIVE_IND FROM (CAL_TBL AS CAL
CROSS JOIN POLICY_PROP AS PRP)
LEFT OUTER JOIN #Policy AS PLY
ON PRP.PolNumber = PLY.PolNumber
AND CAL.CAL_DATE BETWEEN PLY.START_DATE AND PLY.END_DATE)

SELECT CAL_DATE, SUM(ACTIVE_IND) AS ACTIVE_VOL FROM POLICY_STAT
GROUP BY CAL_DATE
ORDER BY CAL_DATE ASC
OPTION (MAXRECURSION 0); /*Must be on the outer select statement*/