Number of times a specific day of month occurs between 2 dates?

Question

Number of times a specific day of month occurs between 2 dates?

rarara

SSCommitted

Points: 1538
More actions
March 27, 2015 at 9:14 am

#150332

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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You could do some fancy date arithmetic, but it's probably easier just to create a calendar table and join to that where the day datepart is 1.

John

rarara SSCommitted Points: 1538 More actions · Answer 2

Hi John,

Can you maybe explain what you mean by a calendar table?

Also, what is the easy way to add a zero to the single digit numericals ie 7 becomes 07 etc? :ermm:

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Here's one example[/url]. There are many more out there if you search for them. You may find it helps to add other columns to the table to suit your needs.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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