Calculating live policy counts (quite advanced!)

Transact-SQL
1

[code]DECLARE @Policies AS TABLE
(
PolicyNo VARCHAR (20),
MinDate VARCHAR (20),
MaxDate VARCHAR (20)
)

INSERT INTO @Policies
VALUES
('DC2878642RIDZ', '20130527_A_CREATE', '20131003_C_CANCEL'),
('EC2878670RACZ', '20130527_A_CREATE', '20130527_A_CREATE'),
('EC2937806RACZ', '20130725_A_CREATE', '20150722_B_RENEWAL'),
('EC2777421RIDZ', '20130811_C_CANCEL', '20130811_C_CANCEL'),
('EC2742041RIDZ', '20131021_B_RENEWAL', '20131021_B_RENEWAL'),
('EC2617863RACZ', '20130226_B_RENEWAL', '20130905_C_CANCEL');

SELECT * FROM @Policies[/code]

I need to calculate a live policy count by day for each day that a policy is active (based on data from 1st January 2013).

Criteria:

Record 1 went live on 27th May 2013 but cancelled on 3rd October 2013 so needs to be counted for every day between 27th May 2013 and 2nd October 2013.

Record 2 went live on 27th May 2013 and because mindate and maxdate are identical the policy was live for an entire year before lapsing naturally so needs to be counted for every day between 27th May 2013 and 26th May 2014.

Record 3 went live on 25th July 2013 and the latest entry is a renewal on 22nd July 2015 meaning it needs to be counted for every day between 25th July 2013 and today.

Record 4 was cancelled on 11th August 2013 and so needs to be counted for every day between 1st January 2013 and 10th August 2013.

Record 5 was renewed on 21st October 2013 meaning it needs to be counted for every day between 1st January 2013 and 20th October 2013 (as it has to be a live policy to be capable of being renewed) but also because mindate and maxdate are identical the policy was live for another entire year before lapsing naturally so also needs to be counted for every day between 21st October 2013 and 20th October 2014.

Record 6 was renewed on 26th February 2013 meaning it needs to be counted for every day between 1st January 2013 and 26th February 2013 (as it has to be a live policy to be capable of being renewed) but was cancelled on 5th September 2013 so also needs to be counted for every day between 27th February 2013 and 4th September 2013.

Any help appreciated, many thanks.

2

This might get you started:

with cte
  as (select policyno
            ,mindate
            ,maxdate
            ,case
                when right(mindate,6)='CREATE'
                then cast(left(mindate,8) as date)
                else cast('2013-01-01' as date)
             end as mindate2
            ,case
                when right(maxdate,6)='CANCEL'
                then dateadd(day,-1,cast(left(maxdate,8) as date))
                else dateadd(day,-1,dateadd(year,1,cast(left(maxdate,8) as date)))
             end as maxdate2
        from @policies
     )
select policyno
      ,mindate
      ,maxdate
      ,mindate2
      ,case
          when maxdate2>current_timestamp
          then cast(current_timestamp as date)
          else maxdate2
       end as maxdate2
      ,datediff(day
               ,mindate2
               ,case
                   when maxdate2>current_timestamp
                   then cast(current_timestamp as date)
                   else maxdate2
                end
               ) as calcdays
  from cte
;
1 Like