CODEWITHSUNDEEP
sqlsql-servert-sql
Serdia
Serdia

Reputation: 4418

How to create development month calendar between two dates

Building loss development triangle I want to create a calendar that would bring all possible development months between two dates.

In my data I have available two dates: EffectiveDate and TransactionDate. TransactionDate is always greater than EffectiveDate.

So I am doing it like this:

IF OBJECT_ID('tempdb..#Calendar') IS NOT NULL DROP TABLE #Calendar
create table #Calendar (EffDate date, TransactDate date) 
INSERT INTO #Calendar values 
                             ('2019-08-01', '2019-11-22'),
                             ('2018-02-06', '2019-01-16'),
                             ('2018-10-22', '2019-02-27'),
                             ('2018-07-23', '2019-03-06'),
                             ('2018-03-01', '2019-05-10'),
                             ('2019-04-09', '2020-04-28'),
                             ('2019-03-01', '2020-05-06'),
                             ('2019-06-28', '2020-06-22'),
                             ('2018-09-26', '2020-07-28')
select YEAR(EffDate) as EffYear,
YEAR(TransactDate) as TransactYear,
        CASE WHEN 
            CEILING(  DATEDIFF(DD,DATEFROMPARTS(YEAR(EffDate), 12, 31),TransactDate) /CONVERT(DECIMAL(4,2),30)) <= 0 THEN 1 
            ELSE CEILING(DATEDIFF(DD,DATEFROMPARTS(YEAR(EffDate), 12, 31),TransactDate) /CONVERT(DECIMAL(4,2),30))  END 
            as DevMonth
from #Calendar

But how can I create a calendar table so then I can LEFT JOIN to it and have all possible values?

I tried something like this, but it taking some time:

 IF OBJECT_ID('tempdb..#YearCalendar') IS NOT NULL DROP TABLE #YearCalendar
    SELECT TOP (DATEDIFF(DAY,CONVERT(DATE,'2018-01-01'),CONVERT(DATE,GETDATE() + 10)))
    'As_Of_Date' = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, CONVERT(DATE,'2018-01-01'))
into #YearCalendar
FROM sys.all_objects a
CROSS JOIN sys.all_objects b

IF OBJECT_ID('tempdb..#DateCalendar') IS NOT NULL DROP TABLE #DateCalendar

    SELECT TOP (DATEDIFF(DAY,CONVERT(DATE,'2018-01-01'),CONVERT(DATE,GETDATE() + 10)))
    'As_Of_Date' = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, CONVERT(DATE,'2018-01-01'))
into #DateCalendar
FROM sys.all_objects a
CROSS JOIN sys.all_objects b



declare @MinYearNum int = (SELECT YEAR(MIN(As_Of_Date)) from #DateCalendar),
        @MinMonthNum int = (SELECT MONTH(MIN(As_Of_Date)) from #DateCalendar)

select 
* from 
(
select distinct  
Year(b.As_Of_Date) as AsOfYear,

(YEAR(a.As_Of_Date) - YEAR(b.As_Of_Date)) * 12 + 12 as PolicyDevYear,


CASE WHEN CEILING (
    DATEDIFF(DD,DATEFROMPARTS(YEAR(b.As_Of_Date), 12, 31),a.As_Of_Date) / CONVERT(DECIMAL(4,2),30) ) <= 0 then 1 else 
    CEILING (
    DATEDIFF(DD,DATEFROMPARTS(YEAR(b.As_Of_Date), 12, 31),a.As_Of_Date) / CONVERT(DECIMAL(4,2),30) ) 
    END AS DevMonth
from  #DateCalendar a
cross join 
(select  [As_Of_Date] =  DATEFROMPARTS(YEAR(As_Of_Date), 12, 31) from #YearCalendar) b
) cal
order by AsOfYear, DevMonth

Upvotes: 1

Views: 180

Answers (1)

SteveC
SteveC

Reputation: 6015

This solution uses a tally table from this link. For each row in #Calendar it generates all of the days between the two dates in the table.

select 
  YEAR(EffDate) as EffYear,
  YEAR(TransactDate) as TransactYear,
  diff.day_diff_count,
  dateadd(d, t.n, EffDate) all_dt
from
  #Calendar
 cross apply
  (select datediff(D, EffDate, TransactDate) day_diff_count) diff
 cross apply
  dbo.fnTally(0, diff.day_diff_count) t;

EDIT: The OP wanted to set the daterange between a beginning date and the current date.

drop table if exists #Calendar;
go
create table #Calendar(
  day_dt date unique not null);

declare
  @start_dt         date='2017-01-01',
  @end_dt           date=getdate();

insert #Calendar(day_dt)
select dateadd(d, t.n, @start_dt) all_dt
from
  (select datediff(D, @start_dt, @end_dt) day_diff_count) diff
 cross apply
  dbo.fnTally(0, diff.day_diff_count) t;

Results

  (1320 row(s) affected)

Upvotes: 1

Related Questions