Solved

SQL Stored Procedure to INSERT start and end dates of Payperiods

Posted on 2016-10-19
6
54 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
ID: 41851280
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 25

Expert Comment

by:Pawan Kumar
ID: 41851330
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 46

Expert Comment

by:Vitor Montalvão
ID: 41851663
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 41852456
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
ID: 41853899
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 46

Expert Comment

by:Vitor Montalvão
ID: 41853904
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

863 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

25 Experts available now in Live!

Get 1:1 Help Now