Solved

SQL Stored Procedure to INSERT start and end dates of Payperiods

Posted on 2016-10-19
6
69 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 28

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 48

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 48

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

839 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