How to get active totals by date

[buzza1234]

Good Morning ,

I have a policy table with below structure. I would like to get how many active policies by day (running total may be).

Create Table #Policy (PolNumber int, StartDate date, EndDate date, Status varchar(10))

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

Select * from #policy where status = 'Active' which means enddate> currentdate. we need to provide running totals how many policy active on particular day

expecting outcome is, (from the Min of startday (only active) till max(enddate) daily running total of active polcies

Date ActivePolicies
2022-01-01 1
2022-01-02 1
2022-01-03 1
2022-01-04 1
2022-01-05 1
2022-01-06 1
,......
.....
2023-01-04 2 (as of 2023-01-04 policy 101 and 100 active)
2023-01-05 2
2023-01-06 2
.........
2023-06-24 3 (101,100, 104 active)
2023-06-25 3
..............
2024-01-01 3 (as of 2024-01-01 policy 101, 100, 104 active)
....
2024-02-01 4 (as of 2024-01-01 policy 101, 100, 104, 102 active)
......
2024-02-22 3 (100 enddate become effect so only 3 active)

Can you please help with this.

Thank you in advance

Asiti

[#2]

Can Somebody please help?

Thanks

Asita

[#3]

Your question is a bit confusing. Are you asking how to have the output structured in a specific way?
One thing I would suggest is to change your active column from a Varchar to a bit field, 1= True and 0 = False. So column "IsActive" with 1's and 0's. Then in your WHERE clause you can say WHERE IsActive = 1.

[#4]

@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*/