rarara (3/27/2015)
Hi, am trying to get to grips with the syntax needed for this...here is one set of example values...StartDate(this is numeric): 20150305
EndDate(this is numeric): 20150905
MonthlyPaymentDay: 1
PaymentAmount: 125
How do I calculate how many times after the start date and before the end date that the 1st of a month occurs (which will trigger a payment)? I suppose I probably want some form of datediff(month) calculation but am a bit stumped.
This above will obviously be 6*125, but I need a select statement to do this across a table.
Also, the calculation should also be clever enough to assume that if say the MonthlyPaymentDay is the 31st that if a month is less than that number of days (February!), to still count it as a whole month.
You might get something useful from playing with this:
DECLARE @StartDate DATE, @EndDate DATE, @MonthlyPaymentDay TINYINT
SELECT @StartDate = '20150305', @EndDate = '20150905', @MonthlyPaymentDay = 1
SELECT MonthlyPaymentDays = RawMonths
- (CASE WHEN @MonthlyPaymentDay < StartDay THEN 1 ELSE 0 END)
- (CASE WHEN @MonthlyPaymentDay > EndDay THEN 1 ELSE 0 END)
FROM (
SELECT
RawMonths = 1 + DATEDIFF(MONTH,@StartDate,@EndDate),
StartDay = DAY(@StartDate),
EndDay = DAY(@EndDate)
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden