Generating multiple rows from a single record with start and end columns

Working on a project where the reporting requirement is to take a single record containing the overall total, the start and end period and generate multiple rows including all periods between the start and end. I knew that I could get the results that I needed with a common table expression (CTE) but time was of the essence so I turned to my trusty friend the ‘Cursor’. I know Cursors are for the weak and the lazy, but i needed to prototype something really fast and for me this was the easiest way. For those of you wondering why is he blogging about being weak and lazy, I did return to the CTE method and was rewarded with the required results using a fraction of the SQL and produced a stored procedure which is easier to modify and tweak as reporting requirements change.

Below you will find some sample code used to generate multiple rows from a single datarow containing a start and an end column. In this example I show both using integers and dates to denote the beginning and end and end values.

/*using integers as starting and ending values*/
create table #cteTempInt
(
id int identity (1,1)
,amount numeric(18,2)
, Divisor int
, starting int
, ending int
)

insert into #cteTempInt Values(1200, 6, 1, 6)
insert into #cteTempInt Values(4300, 10, 2, 11)
insert into #cteTempInt Values(2400, 4, 3, 6)

print 'results from integer sample'
;with t(id, periodAmount, period, ending) AS
(
select id, amount/divisor as periodAmount, starting as period, ending from #cteTempInt
union all
select id, periodAmount, period +1 as period , ending from t
where period+1 <= ending
)

select id, periodamount, period,
row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period

DROP TABLE #cteTempInt

/*using dates as starting and ending values*/

create table #cteTempDate
(
id int identity (1,1)
,amount numeric(18,2)
, Divisor int
, starting date
, ending date
)

insert into #cteTempDate Values(1200, 6, '2012-01-01','2012-06-01')
insert into #cteTempDate Values(4300, 10,'2012-02-01','2012-11-01')
insert into #cteTempDate Values(2400, 4, '2012-05-01','2012-08-01')

print 'results from date sample'
;with t(id, periodAmount, period, ending) AS
(
select id, amount/divisor as periodAmount, starting as period, ending from #cteTempDate
union all
select id, periodAmount, dateadd(mm, 1, period) as period , ending from t
where dateadd(mm,1,period) <= ending
)
select id, periodamount, period,
row_number() over(partition by id order by id,period) as paymentNumber from t order by id, period

drop table #cteTempDate
results from integer sample
id          periodamount                            period      paymentNumber
----------- --------------------------------------- ----------- --------------------
1           200.0000000000000                       1           1
1           200.0000000000000                       2           2
1           200.0000000000000                       3           3
1           200.0000000000000                       4           4
1           200.0000000000000                       5           5
1           200.0000000000000                       6           6
2           430.0000000000000                       2           1
2           430.0000000000000                       3           2
2           430.0000000000000                       4           3
2           430.0000000000000                       5           4
2           430.0000000000000                       6           5
2           430.0000000000000                       7           6
2           430.0000000000000                       8           7
2           430.0000000000000                       9           8
2           430.0000000000000                       10          9
2           430.0000000000000                       11          10
3           600.0000000000000                       3           1
3           600.0000000000000                       4           2
3           600.0000000000000                       5           3
3           600.0000000000000                       6           4

results from date sample
id          periodamount                            period     paymentNumber
----------- --------------------------------------- ---------- --------------------
1           200.0000000000000                       2012-01-01 1
1           200.0000000000000                       2012-02-01 2
1           200.0000000000000                       2012-03-01 3
1           200.0000000000000                       2012-04-01 4
1           200.0000000000000                       2012-05-01 5
1           200.0000000000000                       2012-06-01 6
2           430.0000000000000                       2012-02-01 1
2           430.0000000000000                       2012-03-01 2
2           430.0000000000000                       2012-04-01 3
2           430.0000000000000                       2012-05-01 4
2           430.0000000000000                       2012-06-01 5
2           430.0000000000000                       2012-07-01 6
2           430.0000000000000                       2012-08-01 7
2           430.0000000000000                       2012-09-01 8
2           430.0000000000000                       2012-10-01 9
2           430.0000000000000                       2012-11-01 10
3           600.0000000000000                       2012-05-01 1
3           600.0000000000000                       2012-06-01 2
3           600.0000000000000                       2012-07-01 3
3           600.0000000000000                       2012-08-01 4
Advertisements