Gopal Biswas Gopal Biswas - 26 days ago 11
SQL Question

Sql query to generate monthly pay dates based on a month end date

I want to generate monthly pay dates based on a "month pay" and "month end" date for a year.Suppose I have selected "01/26/2016" as "pay date" and "01/31/2016" as "month end" date and date range is the year 2016.
Then the "month end" date will be the last day of the month for the year and pay dates will be ("month end" date of the month - "pay date" of month) th for other months throughout the year 2016. I have added a screenshot for the result set for the pay date "01/26/2016" and "01/31/2016" as "month end" date. My SQL version is 2012.
I want to know the query in SQL.

Any help will be appreciated.

enter image description here

screenshot for "02/08/2016" as "pay date" and "01/31/2016" as "month end"
enter image description here

Answer

Same logic with your last question.

You can as the below:

DECLARE @StartDate DATETIME = '2016.01.26'
DECLARE @EndDate DATETIME = '2016.01.31'

SELECT
    DATENAME(dw, A.Month) DayNameOfMonth,
    A.[Month],
    DATENAME(dw, A.[Pay Date]) DayNameOfPayDate,
    A.[Pay Date]
FROM
(
    SELECT
         DATEADD(DAY, -1 - (DAY(EOMONTH(@EndDate)) - DAY(@EndDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@EndDate), 1, 1))) 'Month',
         DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1))) 'Pay Date'
    FROM
        (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS v(m)
) A

Result:

DayNameOfMonth                 Month      DayNameOfPayDate               Pay Date
------------------------------ ---------- ------------------------------ ----------
Sunday                         2016-01-31 Tuesday                        2016-01-26
Monday                         2016-02-29 Wednesday                      2016-02-24
Thursday                       2016-03-31 Saturday                       2016-03-26
Saturday                       2016-04-30 Monday                         2016-04-25
Tuesday                        2016-05-31 Thursday                       2016-05-26
Thursday                       2016-06-30 Saturday                       2016-06-25
Sunday                         2016-07-31 Tuesday                        2016-07-26
Wednesday                      2016-08-31 Friday                         2016-08-26
Friday                         2016-09-30 Sunday                         2016-09-25
Monday                         2016-10-31 Wednesday                      2016-10-26
Wednesday                      2016-11-30 Friday                         2016-11-25
Saturday                       2016-12-31 Monday                         2016-12-26

UPDATED

DECLARE @StartDate DATETIME = '2016.01.31' -- month end
DECLARE @EndDate DATETIME = '2016.02.08' -- pay date

SELECT         
    DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1))) 'Month End',        
    DATEADD(DAY, DATEDIFF(DAY, @StartDate, @EndDate), DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1)))) 'Pay Date'
FROM
    (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS v(m)

UPDATE 2

DECLARE @StartDate DATETIME = '2016.01.31' -- month end
DECLARE @EndDate DATETIME = '2016.02.08' -- pay date

SELECT
    *
FROM
(
    SELECT         
        DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1))) 'Month End',        
        DATEADD(DAY, DATEDIFF(DAY, @StartDate, @EndDate), DATEADD(DAY, -1 - (DAY(EOMONTH(@StartDate)) - DAY(@StartDate)),  DATEADD(MONTH, v.m, DATEFROMPARTS(YEAR(@StartDate), 1, 1)))) 'Pay Date'
    FROM
        (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS v(m)
) A
WHERE
    YEAR(A.[Pay Date]) = YEAR(@EndDate)
Comments