CODEWITHSUNDEEP
sqlt-sql
user1810355
user1810355

Reputation: 135

Split rows by months

I have a big data set (100+ mil. rows) that i need to split to rows based on the number of months between the start_date and end_date for accounting purposes.

So for example this record should split into 3612 rows because theres 3612 months between start_date and end_date:

enter image description here

The result I am after should look like this:

enter image description here

I know I can achieve this using recursive CTE, but it takes FOREVER for dataset this big. Any more effective ways to do this?

Upvotes: 0

Views: 426

Answers (3)

ASH
ASH

Reputation: 20362

I just had to do the exact same thing less than 1 week ago!

-----DLL

CREATE TABLE Reporting_Table (
    Credit_Line_NO    Varchar(10),
    noMonths         INT,
    EFFECTIVEDATE    Date,
    EXPIRY_DATE      Date,
    Amount           Money,
    mxDays           INT,
    mxFactor         decimal(5,4),
    Calc             Money)

INSERT INTO Reporting_Table (Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc)
 Values('9938810','3','3/31/2018','6/12/2020','11718.75','90','1','11718.75') 

INSERT INTO Reporting_Table (Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc)
 Values('2235461','1','6/30/2018','6/6/2019','12345','30','1','12345') 

INSERT INTO Reporting_Table (Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc)
 Values('3365434','12','6/30/2018','6/30/2019','298523.36085','365','1.01388888888889','302669.518639583') 

---- VIEW DATA SET . . .
 Select *, (DATEDIFF(MONTH,EFFECTIVEDATE,EXPIRY_DATE)/noMonths)+1 as FREQ
 From Reporting_Table

-- Now that you have a working data set to fiddle around with, simply expand all relevant tows.

;with cte as
(
  select Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc,
         (DATEDIFF(MONTH,EFFECTIVEDATE,EXPIRY_DATE)/noMonths)+1 as FREQ,
         1 as Rec_Iteration
  from Reporting_Table
  where noMonths > 1
  union all  
  select Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc,
         (DATEDIFF(MONTH,EFFECTIVEDATE,EXPIRY_DATE)/noMonths)+1 as FREQ,
         Rec_Iteration + 1
  from cte 
  where Rec_Iteration < FREQ
)
select Credit_Line_NO, noMonths, EFFECTIVEDATE, EXPIRY_DATE, Amount, mxDays, mxFactor, Calc,
    (DATEDIFF(MONTH,EFFECTIVEDATE,EXPIRY_DATE)/noMonths)+1 as FREQ
from cte
order by Credit_Line_NO,
         Rec_Iteration

Upvotes: 0

paparazzo
paparazzo

Reputation: 45106

declare @numbers TABLE (ID int NOT NULL primary key);  
INSERT INTO  @numbers (ID)
VALUES
   (1)
  ,(2)
  ,(3)
  ,(4)
  ,(5)
  ,(6)
  ,(7)
  ,(8);

declare @range TABLE (cnt int);  
INSERT INTO  @range (cnt)
VALUES (1), (4), (6);

select r.cnt, n.ID
from @range r
join @numbers n 
  on n.ID < = r.cnt 
order by r.cnt, n.ID;

Upvotes: 0

uzi
uzi

Reputation: 4146

In this query I'm using master.dbo.spt_values as tally table. But it will not suit you since it has numbers only till 2048. So you must create your own number table and change master.dbo.spt_values with it in the query.

declare @t table (
    id int
    , start_date date
    , end_date date
    , months int
)

insert into @t
values (1, '19990101', '21000101', 1212)

select
    t.id, month_num = v.number + 1
    , total_days = sum(datediff(dd, dateadd(mm, v.number, t.start_date), dateadd(mm, v.number + 1, t.start_date))) over (partition by t.id)
    , t.start_date, t.end_date
from
    @t t
    join master.dbo.spt_values v on t.months > v.number
where
    v.type = 'P'

Upvotes: 3

Related Questions