Help with loop/while or similar solution

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with loop/while or similar solution

Author

Topic

luzippu
Starting Member

23 Posts

Posted - 2014-08-03 : 05:19:33

Hi,

I need some help with some code!

Scenario:
I know the ContractNumber
I know the StartDate of a contract (always in the past)
I know the RentalAmount (fixed for the duration)
I know the RentalFrequency (Monthly, Quarterly, Annual from StartDate)

I need the code to recreate all payments dates (RentalDate) to date, showing value paid at each interval.

Output would look something like this:

eg.
ContractNumber = 12345
StartDate = 01/01/2013
RentaAmount = 100
RetalFrequency = Quarterly

ContractNumber RentalDate RentalAmount
12345 01/01/2013 100
12345 01/04/2013 100
12345 01/07/2013 100
12345 01/10/2013 100
12345 01/01/2014 100
12345 01/04/2014 100
12345 01/07/2014 100

Any help would be appreciated,

Thanks

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-03 : 17:11:59

DECLARE @ContractNumber varchar(10) = 12345,
@StartDate datetime = '01/01/2013',
@RentaAmount money = '100.00',
@RentalFrequency varchar(15)= 'Quarterly',
@dt datetime = GETDATE()
DECLARE @RentalFrequncyNumber int = CASE @RentalFrequency
WHEN 'Monthly' THEN 1
WHEN 'Quarterly' THEN 3
WHEN 'Annual' THEN 12
END

;With MyCTE
AS
(
SELECT @ContractNumber ContractNumber, DATEADD(mm,0,@StartDate) StartDate,@RentaAmount RentaAmount
UNION ALL
SELECT ContractNumber, DATEADD(mm,@RentalFrequncyNumber,StartDate) StartDate, RentaAmount
FROM MyCTE
WHERE DATEADD(mm,@RentalFrequncyNumber,StartDate) <=@dt
)

SELECT * FROM MyCTE
OPTION (MAXRECURSION 0 )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources