SQLTeam.com | Weblogs | Forums

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

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

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

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

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