Joining dates to calculate payment month dates

Question

Joining dates to calculate payment month dates

traj0130

SSC Journeyman

Points: 86
More actions
August 2, 2018 at 6:00 pm

#414509

Hi I need some help and new to SQL and this forum.Please advise.
I have a scenario if an employee joins before 15 th or after 15th of a given month
Eg
If a person Joins on 01/10/2018 I need to add a month to it which is 02/09/2018 and say its MonthZero
and from Feb 10th to feb 28th he comes Under MonthOne and From March 1st to 31st he comes under Month3 and so on
If He Joins after 15th(01/16/2018) of the month ,MonthZero will start from 1st of next month (02/01/2018)and Monthone from 1st of the the following date(03/01//2018) and so on
How to write a case statement for above scenario.Please help .
Thank you for your time.

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

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

andycadley SSCertifiable Points: 5296 More actions · Answer 1

Something like:

Drop Table If Exists #Temp
Create Table #Temp
(
 Id int identity(1,1),
 StartDate Date Not Null,
 MonthZeroStart As Case
  When DatePart(Day, StartDate) < 15 Then StartDate
  Else DateAdd(Month, DateDiff(Month, 0,DateAdd(Month, 1, StartDate)),0)
 End Persisted
)
Set DateFormat mdy
Insert Into #Temp(StartDate)
Values('01/10/2018'),('02/16/2018')

Select
id, MonthZeroStart, DateAdd(Month, 1, MonthZeroStart) As MonthOneStart, DateAdd(Month, 2, MonthZeroStart) As MonthTwoStart
From #Temp

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

traj0130 - Thursday, August 2, 2018 6:00 PM

First of all, CASE is an expression and not a statement. Expressions return single values of a known data type. Secondly, the only display format allowed in ANSI/ISO standard SQL is based on ISO 8601 standards;yyyy-mm-dd.

But more to your question, have you thought about having a calendar table? This calendar table could include a column that holds the membership month number for each date. This will give you a little more flexibility for handling weekends, holidays, and changes in procedure. You can use a spreadsheet to set up your calendar table in a matter of minutes.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 3

jcelko212 32090 - Sunday, August 5, 2018 3:52 PM
Secondly, the only display format allowed in ANSI/ISO standard SQL is based on ISO 8601 standards;yyyy-mm-dd.

How many times do we have to prove how wrong you are there, Joe? Get a copy of the very standard that you keep citing and read it! Stop trying to do things from memory... we're both getting too old to have much of one left. Read the damned standard. 😉 If you're talking about some standard that's a derivative of ISO 8601, then please do post the document number so that we can try to look it up.

Also, that "standard" of YYYY-MM-DD doesn't work correctly if the language for SQL Server is French or a couple of others.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)