Solved

SQL Stored Procedure to INSERT start and end dates of Payperiods

Posted on 2016-10-19
6
46 Views
Last Modified: 2016-10-21
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
Comment
Question by:huerita37
6 Comments
 
LVL 20

Expert Comment

by:Russ Suter
Comment Utility
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
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:huerita37
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now