Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

SQL Stored Procedure to INSERT start and end dates of Payperiods

There will be two payperiods for each month.  The first payperiod of the month is 1st-15th.  The second payperiod of the month is 16th-last day of month.

How do I find these dates and how do I INSERT the start and end date of each payperiod into the DB?

Something like the following:

WITH DATES AS
(
    SELECT '01/01/2026' AS StartDate
    UNION ALL
    SELECT DATEADD(d, 1, StartDate) + 14 AS EndDate
    FROM DATES
    WHERE DATEADD(d, 1, StartDate) < DATEADD(m, 1, '12/31/2030')
)

INSERT INTO tblWP_TS_PayPeriod
(PayPeriod, StartingPayPeriodDate, EndingPayPeriodDate)
SELECT NewID(), StartDate, EndDate
FROM DATES
WHERE EndDate <= '12/31/2030'

Open in new window

0
huerita37
Asked:
huerita37
1 Solution
 
Russ SuterCommented:
I'll use December, 2016 as an example.
DECLARE @PayMonth DATE
DECLARE @Period1Start DATE
DECLARE @Period1End DATE
DECLARE @Period2Start DATE
DECLARE @Period2End DATE

SET @PayMonth = '12/01/2016'
SET @Period1Start = @PayMonth
SET @Period1End = DATEADD(DAY, 14, @Period1Start)
SET @Period2Start = DATEADD(DAY, 1, @Period1End)
SET @Period2End = DATEADD(DAY, -1, DATEADD(MONTH, 1, @PayMonth))

PRINT @Period1Start
PRINT @Period1End
PRINT @Period2Start
PRINT @Period2END

Open in new window

Using type DATE here instead of type DATETIME is useful since you don't have to worry about invalid ranges dealing with time values.
0
 
Pawan KumarDatabase ExpertCommented:
try...

DECLARE @startYear AS INT = 2016
DECLARE @EndYear AS INT = 2030

SELECT * FROM 
(
	SELECT DATEADD(M, Number-1 ,DATEFROMPARTS(@StartYear,1,1)) FirstPayingStartDate, DATEADD(d,14,DATEADD(M, Number-1 ,DATEFROMPARTS(@StartYear,1,1))) FirstPayingEndDate
	,DATEADD(d,15,DATEADD(M, Number-1 ,DATEFROMPARTS(@StartYear,1,1))) SecondPayingStartDate , EOMONTH(DATEADD(M, Number-1 ,DATEFROMPARTS(@StartYear,1,1))) SecondPayingEndDate
	FROM
	(
		SELECT DISTINCT Number 
		FROM 
			MASTER..spt_values 
		WHERE 
			Number > 0 AND Number < 10000
	)m	
)l
WHERE FirstPayingStartDate <= DATEFROMPARTS(2030,'12','31')

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
This version doesn't need to access any table so it should be very fast (example for this year until end of 2020 - you can change the period by changing directly the variable values):
DECLARE @dt1 DATE='20160101'
DECLARE @dt2 DATE='20201231'
;WITH ctePeriodGenerator (BeginPeriod, EndPeriod)
     AS (SELECT @dt1, DATEADD(day,14,@dt1)
         UNION ALL
         SELECT DATEADD(day,1,EndPeriod),
		CASE
			WHEN DATEPART(day,EndPeriod)=16 THEN EOMONTH(EndPeriod)
			ELSE EOMONTH(DATEADD(day,1,EndPeriod))
		END 
         FROM   ctePeriodGenerator 
         WHERE  EndPeriod < @dt2) 
INSERT INTO tblWP_TS_PayPeriod (PayPeriod, StartingPayPeriodDate, EndingPayPeriodDate)
SELECT NEWID(), BeginPeriod, EndPeriod 
FROM ctePeriodGenerator 

SELECT *
FROM tblWP_TS_PayPeriod

Open in new window

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Scott PletcherSenior DBACommented:
I used a "standard" in-line tally table to generate a separate row for every month:

DECLARE @StartDate date
DECLARE @EndDate date
SET @StartDate = '20260101'
SET @EndDate = '20301231'

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS Numbers(Number)
),
cteTally100 AS (
    SELECT 0 AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
),
cteTally10K AS (
    SELECT ROW_NUMBER() OVER(ORDER BY c1.number) - 1 AS number
    FROM cteTally100 c1
    CROSS JOIN cteTally100 c2
)
--INSERT INTO tblWP_TS_PayPeriod
--(PayPeriod, StartingPayPeriodDate, EndingPayPeriodDate)
SELECT
    NEWID(),  
    DATEADD(DAY, CASE WHEN DAY(paydate) = 15 THEN 0 ELSE 15 END, DATEADD(MONTH, month.number, @StartDate)) AS StartingPayPeriodDate,
    paydate AS EndingPayPeriodDate
FROM cteTally10K month
/*generate separate rows for each pay period in the month*/
CROSS APPLY (
    VALUES(DATEADD(DAY, 14, DATEADD(MONTH, month.number, @StartDate))),
          (DATEADD(DAY, -1, DATEADD(MONTH, month.number + 1, @StartDate)))
) AS paydates(paydate)
/*use tally table to generate a separate row for every month*/
WHERE month.number BETWEEN 0 AND DATEDIFF(MONTH, @StartDate, @EndDate)
0
 
huerita37Author Commented:
Thank you Scott.  You gave me exactly what I needed.

When I tried using the query with DATEFROMPARTS or EOMONTH I got an error saying that is was not a recognized built-in function name.  I am using SQL 2012.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
EOMONTH is available in MSSQL 2012.
Anyway, on the future questions please add always your SQL Server version so the answers will be more accurate.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now