Solved

SQL Stored Procedure to INSERT start and end dates of Payperiods

Posted on 2016-10-19
6
75 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 49

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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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 49

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

Technology Partners: 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!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

726 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