March 17, 2017 at 3:30 pm
I want to do something like this in sql. I have a table in sql with startDate and enddate. I want to insert our start and end pay periods startDate and end Date in sql for the current month and the next month automatically with a query
Our pay period starts at 12/08/2016
so for e.g right now, I inserted these values by hand in sql:
ID Start date end Date
120 2017-03-02 00:00:00 2017-03-15 00:00:00
119 2017-02-16 00:00:00 2017-03-01 00:00:00
118 2017-02-02 00:00:00 2017-02-15 00:00:00
117 2017-01-19 00:00:00 2017-02-01 00:00:00
116 2017-01-05 00:00:00 2017-01-18 00:00:00
115 2016-12-22 00:00:00 2017-01-04 00:00:00
114 2016-12-08 00:00:00 2016-12-21 00:00:00
I want to put some logic in a query so that wehenever the query is called, values for start date and end date gets inserted in the table for this month and next month if they don't exists in the table.The startDate and end date interval is 14 days.
here is what I have so far
Declare @StartDate varchar(10), @StartYear varchar(10)
Set @StartYear = year(getdate())
Set @StartDate= '12/08/2016'
DECLARE @cnt INT = 0;
WHILE @cnt < cnt_total
Insert into tbl_PayPeriod
(@StartDate, dateAdd(day, 13,@StartDate))
I just couldn't figure out how to continually add startDate and end date in the table.
any help will be appreciated.
March 18, 2017 at 1:00 am
Firstly, start off by using Jeff Moden's Tally function
CREATE FUNCTION [dbo].[fnTally]
/**********************************************************************************************************************
Purpose:
Return a column of BIGINTs from @ZeroOrOne up to and including @MaxN with a max value of 1 Trillion.
As a performance note, it takes about 00:02:10 (hh:mm:ss) to generate 1 Billion numbers to a throw-away variable.
Usage:
--===== Syntax example (Returns BIGINT)
SELECT t.N
FROM dbo.fnTally(@ZeroOrOne,@MaxN) t
;
Notes:
1. Based on Itzik Ben-Gan's cascading CTE (cCTE) method for creating a "readless" Tally Table source of BIGINTs.
Refer to the following URLs for how it works and introduction for how it replaces certain loops.
http://www.sqlservercentral.com/articles/T-SQL/62867/
http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
2. To start a sequence at 0, @ZeroOrOne must be 0 or NULL. Any other value that's convertable to the BIT data-type
will cause the sequence to start at 1.
3. If @ZeroOrOne = 1 and @MaxN = 0, no rows will be returned.
5. If @MaxN is negative or NULL, a "TOP" error will be returned.
6. @MaxN must be a positive number from >= the value of @ZeroOrOne up to and including 1 Billion. If a larger
number is used, the function will silently truncate after 1 Billion. If you actually need a sequence with
that many values, you should consider using a different tool. ;-)
7. There will be a substantial reduction in performance if "N" is sorted in descending order. If a descending
sort is required, use code similar to the following. Performance will decrease by about 27% but it's still
very fast especially compared with just doing a simple descending sort on "N", which is about 20 times slower.
If @ZeroOrOne is a 0, in this case, remove the "+1" from the code.
DECLARE @MaxN BIGINT;
SELECT @MaxN = 1000;
SELECT DescendingN = @MaxN-N+1
FROM dbo.fnTally(1,@MaxN);
8. There is no performance penalty for sorting "N" in ascending order because the output is explicity sorted by
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
Revision History:
Rev 00 - Unknown - Jeff Moden
- Initial creation with error handling for @MaxN.
Rev 01 - 09 Feb 2013 - Jeff Moden
- Modified to start at 0 or 1.
Rev 02 - 16 May 2013 - Jeff Moden
- Removed error handling for @MaxN because of exceptional cases.
Rev 03 - 22 Apr 2015 - Jeff Moden
- Modify to handle 1 Trillion rows for experimental purposes.
**********************************************************************************************************************/
(@ZeroOrOne BIT, @MaxN BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN WITH
E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1) --10^1 or 10 rows
, E4(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d) --10^4 or 10 Thousand rows
,E12(N) AS (SELECT 1 FROM E4 a, E4 b, E4 c) --10^12 or 1 Trillion rows
SELECT N = 0 WHERE ISNULL(@ZeroOrOne,0)= 0 --Conditionally start at 0.
UNION ALL
SELECT TOP(@MaxN) N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E12 -- Values from 1 to @MaxN
;
This code should do the trick
-- The number of days in yor PayPeriod
DECLARE @DAYS_PER_CYCLE INT = 14;
-- The current max StartDate in your table
DECLARE @CurrentMaxStartDate DATE = (SELECT MAX(StartDate) FROM tbl_PayPeriod);
-- The last date that we want to generate data for
DECLARE @FirstOfNextMonth DATE = DATEADD(MM, DATEDIFF(MM, 0, GETDATE())+1, 0);
-- The number of Pay Periods that we will need to generate.
DECLARE @NumCyclesNeeded INT = DATEDIFF(DD, @CurrentMaxStartDate, DATEADD(MM, DATEDIFF(MM, 0, GETDATE())+1, 0)) /@DAYS_PER_CYCLE;
-- We need to check for negatives because
-- 1 - That means that we already have enough data
-- 2 - They break Jeff's function
IF ( @NumCyclesNeeded > 0 )
BEGIN
INSERT INTO tbl_PayPeriod ( StartDate, EndDate )
SELECT
StartDate = DATEADD(DD, t.N *@DAYS_PER_CYCLE, @CurrentMaxStartDate)
, EndDate = DATEADD(DD, (t.N +1) *@DAYS_PER_CYCLE -1, @CurrentMaxStartDate)
FROM dbo.fnTally(1, @NumCyclesNeeded) AS t
ORDER BY t.N; -- Technically we don't need the order by clause, because ROW_NUMBER() in fnTally orders the data.
END;
March 20, 2017 at 6:51 am
Just curious...why wouldn't you just have a calendar table that went so far into the future that you would never have a problem with a missing value?
March 20, 2017 at 7:29 am
heb1014 - Monday, March 20, 2017 6:51 AMJust curious...why wouldn't you just have a calendar table that went so far into the future that you would never have a problem with a missing value?
This is a popular option, however the OP is interested in a row-generator with a date calculator (until stated otherwise), which is exactly what has been offered. A calendar table is most useful when it has a number of columns storing a variety of data including holidays, and that takes maintenance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply