How to get Active counts between dates. Please

Question

How to get Active counts between dates. Please

March 13, 2024 at 12:58 pm

This was removed by the editor as SPAM

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2174 More actions · Answer 1

Jeff Moden wrote:

IMHO, Steve Collins' post (with a tiny tweak (change fn.n to fn.value) in the 2nd bit of code) contains the winning entries for simplicity IF you don't mind any missing dates not showing up.

Ok yes. Using a CTE with the UNION ALL operator 2 passes the input table (which is tiny) and expands across the entire series. This code uses LEAD and SELECT TOP to conditionally expand the input rows to include any missing date gap and sets an is_policy 0/1 indicator column. I changed the input data to include a gap. Is it faster? Idk on my machine both Jeff's code and this only take 1ms

drop table if exists #Policy;
go
Create Table #Policy(
  PolNumber         int,
  StartDate         date,
  EndDate           date,
  Status            varchar(10));

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

set statistics time on;
set statistics io on;

with lead_cte as (
    select *, lead(StartDate) over (order by StartDate) lead_start_dt
    from #Policy
    where [Status]='Active')
select v.calc_dt, sum(row_exp.is_policy) active_policies
from lead_cte lc
     cross apply (values (iif(lc.lead_start_dt>lc.EndDate, datediff(day, lc.EndDate, lc.lead_start_dt), 0))) calc(gap_days)
     cross apply (select top(iif(calc.gap_days>0, 2, 1)) v.day_count, v.is_policy
                  from (values (datediff(day, lc.StartDate, lc.EndDate), 1), 
                               (calc.gap_days, 0)) v(day_count, is_policy)) row_exp(day_count, is_policy)
     cross apply dbo.fnTally(0, row_exp.day_count) fn
     cross apply (values (dateadd(day, fn.n, iif(row_exp.is_policy=1, lc.StartDate, lc.EndDate)))) v(calc_dt)
group by v.calc_dt
order by v.calc_dt;

set statistics io off;
set statistics time off;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 2

Steve Collins wrote:

Jeff Moden wrote:

IMHO, Steve Collins' post (with a tiny tweak (change fn.n to fn.value) in the 2nd bit of code) contains the winning entries for simplicity IF you don't mind any missing dates not showing up.

Ok yes. Using a CTE with the UNION ALL operator 2 passes the input table (which is tiny) and expands across the entire series. This code uses LEAD and SELECT TOP to conditionally expand the input rows to include any missing date gap and sets an is_policy 0/1 indicator column. I changed the input data to include a gap. Is it faster? Idk on my machine both Jeff's code and this only take 1ms

drop table if exists #Policy;
go
Create Table #Policy(
  PolNumber         int,
  StartDate         date,
  EndDate           date,
  Status            varchar(10));

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

set statistics time on;
set statistics io on;

with lead_cte as (
    select *, lead(StartDate) over (order by StartDate) lead_start_dt
    from #Policy
    where [Status]='Active')
select v.calc_dt, sum(row_exp.is_policy) active_policies
from lead_cte lc
     cross apply (values (iif(lc.lead_start_dt>lc.EndDate, datediff(day, lc.EndDate, lc.lead_start_dt), 0))) calc(gap_days)
     cross apply (select top(iif(calc.gap_days>0, 2, 1)) v.day_count, v.is_policy
                  from (values (datediff(day, lc.StartDate, lc.EndDate), 1), 
                               (calc.gap_days, 0)) v(day_count, is_policy)) row_exp(day_count, is_policy)
     cross apply dbo.fnTally(0, row_exp.day_count) fn
     cross apply (values (dateadd(day, fn.n, iif(row_exp.is_policy=1, lc.StartDate, lc.EndDate)))) v(calc_dt)
group by v.calc_dt
order by v.calc_dt;

set statistics io off;
set statistics time off;

Maybe you should try with more test data if you want to see which is faster.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

This reminds me of the good ol' days on these forums... thanks for the memories, folks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)