creating a start and end date table
in [Programming SQL]
|
From: rodchar on 17 Feb 2010 09:51 Hi All, Below is a SQL CTE that takes a start and end date and creates a table of bi-weekly start and dates within the date range. Are there any other ways to write this? More concise? (not saying that it isn't concise because I don't know) ;WITH cteTally AS ( SELECT ROW_NUMBER() OVER (ORDER BY ID)-1 AS N FROM Master.sys.SysColumns ) SELECT DATEADD(dd,t.n*14,'20080307') AS end_date, DATEADD(dd, -14, DATEADD(dd,t.n*14,'20080307')) as start_date INTO #TEMP_DATE_RANGES FROM cteTally t WHERE DATEADD(dd,t.n*14,'20080307') < '20100221' thanks, rodchar
From: Plamen Ratchev on 17 Feb 2010 10:12 What you have will work but I do not think a good idea to use system tables in production code. Better to use permanent table with numbers, or something like this: ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1), N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y), N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y), N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y), Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N4) SELECT DATEADD(DAY, (n - 1) * 14 - 14, '20080307') AS start_date, DATEADD(DAY, (n - 1) * 14, '20080307') AS end_date FROM Nums WHERE DATEADD(DAY, (n - 1) * 14, '20080307') < '20100221'; -- Plamen Ratchev http://www.SQLStudio.com
From: rodchar on 17 Feb 2010 11:21 Hi Plamen, What does the part below do (will I have to maintain that if there are more than 256 rows? ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1) , N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y) , N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y) , N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y) , Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N4) "Plamen Ratchev" wrote: > What you have will work but I do not think a good idea to use system tables in production code. Better to use permanent > table with numbers, or something like this: > > ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1), > N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y), > N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y), > N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y), > Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N4) > SELECT DATEADD(DAY, (n - 1) * 14 - 14, '20080307') AS start_date, > DATEADD(DAY, (n - 1) * 14, '20080307') AS end_date > FROM Nums > WHERE DATEADD(DAY, (n - 1) * 14, '20080307') < '20100221'; > > -- > Plamen Ratchev > http://www.SQLStudio.com > . >
From: Plamen Ratchev on 17 Feb 2010 11:30 This is creating table with numbers on the fly by cross joining CTEs. You can extend very easily: ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1), N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y), N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y), N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y), N5 (n) AS (SELECT 1 FROM N4 AS X, N4 AS Y), N6 (n) AS (SELECT 1 FROM N5 AS X, N5 AS Y), Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N6) .... -- Plamen Ratchev http://www.SQLStudio.com
From: rodchar on 17 Feb 2010 13:34
Thanks for your help, (again) rod. "Plamen Ratchev" wrote: > This is creating table with numbers on the fly by cross joining CTEs. You can extend very easily: > > ;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1), > N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y), > N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y), > N4 (n) AS (SELECT 1 FROM N3 AS X, N3 AS Y), > N5 (n) AS (SELECT 1 FROM N4 AS X, N4 AS Y), > N6 (n) AS (SELECT 1 FROM N5 AS X, N5 AS Y), > Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM N6) > .... > > -- > Plamen Ratchev > http://www.SQLStudio.com > . > |