How to generate rows by month based on loop iterate month from date

Transact-SQL
1

Hi,

I try to do this with 2 solution method, but no luck:-

Example:
ID: 1
PostedDate: 2016-03-01,
Rate: 6 (month)
QPrice: 600

Output:
ID | PostedDate | Date | AmountByMth
1 | 2016-03-01 | 2016-03-01 | 100
1 | 2016-03-01 | 2016-04-01 | 100
1 | 2016-03-01 | 2016-05-01 | 100
1 | 2016-03-01 | 2016-06-01 | 100
1 | 2016-03-01 | 2016-07-01 | 100
1 | 2016-03-01 | 2016-08-01 | 100

Solution 1:
;WITH cte AS (
select
ID,
PostedDate=cast(convert(char(8), PostedDate, 112) + ' 00:00:00.000' as datetime),
DepRateByMth=m.Rate,
DepAmtByMth=QPrice/m.Rate
from tblMaster m
)
SELECT cte.ID, cte.PostedDate, [Date]=k.dateValue, AmountByMonth=cte.DepAmtByMth
FROM dbo.Fn_DateRange k
OPTION (MAXRECURSION 0)

Solution 2:
select * from
(
select
ID,
PostedDate=cast(convert(char(8), PostedDate, 112) + ' 00:00:00.000' as datetime),
DepRateByMth=m.Rate,
DepAmtByMth=QPrice/m.Rate
from tblMaster m
) k
CROSS JOIN dbo.Fn_DateRange

My Date Range Function as:

CREATE FUNCTION [dbo].[Fn_DateRange]
(@startDate AS DATE,
@interval AS INT,
@i int=0
)
RETURNS @Dates TABLE(dateValue DATE)
BEGIN
While @i < @interval
begin
insert into @Dates
SELECT DATEADD(month, @i, @startDate)
WHERE @i<@interval
Set @i = @i + 1
end
RETURN
END

Please advise.

Thank you.

Regards,
Micheale

2

Sorry, Found the solution using CROSS APPLY. Thank you.

3

If you can post the solution, it will be helpful for those who are interested

4

Hi,

Here you go.

CREATE FUNCTION [dbo].[Fn_DateRange]
(@startDate AS DATE,
@interval AS INT,
@i int=0
)
RETURNS @Dates TABLE(dateValue DATE)
BEGIN
While @i < @interval
begin
insert into @Dates
SELECT DATEADD(month, @i, @startDate)
WHERE @i<@interval
Set @i = @i + 1
end
RETURN
END

My solution:

declare @tbl table (
ID int,
PostedDate Date,
Rates int,
Price float
)
insert into @tbl
select ID=1,PostedDate='2016-03-01',Rates=6,Price=600
union
select ID=2,PostedDate='2017-07-01',Rates=12,Price=6005.88

select *,AmountPerMth=Price/Rates from @tbl k
CROSS APPLY dbo.Fn_DateRange ( k.PostedDate,k.Rates,0 ) o

Thanks.

Regards,
Mic

1 Like