August 9, 2016 at 6:07 am
HI,
I looking to create a tmp table of dates as simply as possible in tsql.
Examples online take a lot of lines, I'd like to do it in 2 or 3 lines using row number perhaps, but without looping!
The gist of it is I want to add 4 weeks to a startdate for 1 full year!
@StartDate
@EndDAte = DateAdd(Year,1,@StartDate)
Create #tmpCalendar
(
MonthEnd DATE
) ;
--Pop table something like
INSERT INTO #tmpCalendar
SELECT @StartDate AS MonthEnd
UNION ALL
SELECT DATEADD(Week,4,@StartDate) as MonthEnd
WHERE DATEADD(Week,4,@StartDate) <= @EndDate
OR
INSERT INTO #tmpCalendar
SELECT DATEADD(Week,4*Row_Number,@StartDate) as MonthEnd
WHERE DATEADD(Week,4,@StartDate) <= @EndDate
But I know Im missing something here!
August 9, 2016 at 6:22 am
SELECT DATEADD(week,Weeks,@StartDate) AS [MonthEnd]
FROM (VALUES (0),(4),(8),(12),(16),(20),(24),(28),(32),(36),(40),(44),(48),(52)) w (Weeks)
Far away is close at hand in the images of elsewhere.
Anon.
August 9, 2016 at 7:14 am
Hi David,
That is exactly what I was looking for...
I wonder tho can it be done recursively!
kind regards
August 9, 2016 at 7:16 am
David Burrows (8/9/2016)
SELECT DATEADD(week,Weeks,@StartDate) AS [MonthEnd]
FROM (VALUES (0),(4),(8),(12),(16),(20),(24),(28),(32),(36),(40),(44),(48),(32)) w (Weeks)
Hi David,
I think you have a typo in your values...shouldn't that 32 at the end be a 52?
Cheers,
August 9, 2016 at 7:18 am
Y.B. (8/9/2016)
David Burrows (8/9/2016)
SELECT DATEADD(week,Weeks,@StartDate) AS [MonthEnd]
FROM (VALUES (0),(4),(8),(12),(16),(20),(24),(28),(32),(36),(40),(44),(48),(32)) w (Weeks)
Hi David,
I think you have a typo in your values...shouldn't that 32 at the end be a 52?
Cheers,
Yeah, good spot 🙂
Far away is close at hand in the images of elsewhere.
Anon.
August 9, 2016 at 7:33 am
Tallboy (8/9/2016)
Hi David,That is exactly what I was looking for...
I wonder tho can it be done recursively!
kind regards
WITH
cte (MonthEnd)
AS (
SELECT @StartDate
UNION ALL
SELECT DATEADD(week,4,MonthEnd)
FROM cte
WHERE DATEADD(week,4,MonthEnd) <= @EndDate
)
SELECT MonthEnd
FROM cte
Far away is close at hand in the images of elsewhere.
Anon.
August 9, 2016 at 8:05 am
Even if with this amount of rows it might make no difference, avoid using recursive CTEs to count. Here's another version which might seem too wordy, but it will be able to perform well under most circumstances.
DECLARE @StartDate datetime,
@EndDAte datetime;
SELECT @StartDate = '20160101',
@EndDAte = DateAdd(Year,1,@StartDate);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT DATEADD(Week,4*n,@StartDate) as MonthEnd
FROM cteTally
WHERE DATEADD(Week,4*n,@StartDate) <= @EndDate;
Of course, if you don't want to write the cascade CTEs each time, you can convert them to an ITV function or a view. You could also create a table with its proper clustered index.
August 9, 2016 at 8:25 am
Luis Cazares (8/9/2016)
Even if with this amount of rows it might make no difference, avoid using recursive CTEs to count. Here's another version which might seem too wordy, but it will be able to perform well under most circumstances.
DECLARE @StartDate datetime,
@EndDAte datetime;
SELECT @StartDate = '20160101',
@EndDAte = DateAdd(Year,1,@StartDate);
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT DATEADD(Week,4*n,@StartDate) as MonthEnd
FROM cteTally
WHERE DATEADD(Week,4*n,@StartDate) <= @EndDate;
Of course, if you don't want to write the cascade CTEs each time, you can convert them to an ITV function or a view. You could also create a table with its proper clustered index.
Nice Luis
I originally thought along those lines but chose mine as the original request was restricted to a year and requested in 2 or 3 lines 😉
Far away is close at hand in the images of elsewhere.
Anon.
August 9, 2016 at 8:35 am
Luis Cazares (8/9/2016)
Even if with this amount of rows it might make no difference, avoid using recursive CTEs tocountgenerate rows...
...because they're way too expensive for such a simple process. CTE's are great for resolving hierarchies and for complex counting processes including running totals and moving averages.
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
August 9, 2016 at 8:41 am
ChrisM@Work (8/9/2016)
Luis Cazares (8/9/2016)
Even if with this amount of rows it might make no difference, avoid using recursive CTEs tocountgenerate rows......because they're way too expensive for such a simple process. CTE's are great for resolving hierarchies and for complex counting processes including running totals and moving averages.
Totally agree, thanks for clarifying that. 😉
August 9, 2016 at 8:50 am
Here is technically a two line solution (which can be only 1 if you don't use variables)... 😉
DECLARE @startDate DATE = '20160101', @skipWeeks TINYINT = 4, @maxWeeks TINYINT = 54
SELECT DATEADD(week, number, @startDate) AS myDates FROM master.dbo.spt_values WHERE number <= @maxWeeks AND number % @skipWeeks = 0 AND Type = 'p'
NOTE: This is using an undocumented table and the max value can't exceed 2047.
EDIT: Personally, I would just use a tally table to generate the numbers it's better for a few extra 'lines'. Also instead of hardcoding values you can easily change the variables to output your dates any way you want. Just keep in mind you'll have cross join more values the higher you want your max weeks to go to.
DECLARE @startDate DATE = '20160101', @skipWeeks TINYINT = 4, @maxWeeks TINYINT = 54
;WITH cte(n) AS (
SELECT TOP (@maxWeeks) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
)
SELECT DATEADD(week, n, @startDate) AS myDates FROM cte WHERE n <= @maxWeeks AND n % @skipWeeks = 0
August 9, 2016 at 9:41 am
Thats all true,
But I find a simple example is best when demonstrating/explaining complex functionality such as recursion etc.
Thanks Again guys
August 9, 2016 at 12:35 pm
One last thing. Questions often come up where a count of days is needed excluding special days. You might find it handy to add a column to flag whether or not a day is a holiday or a weekend or a workday. Having such a column makes such queries much simpler.
It's easy to flag weekend days when you create the table. After that, you can update it with 'H' for holidays at your leisure.
;with tally (N) as (select top(10000) ROW_NUMBER() over(order by (select null)) from sys.columns cross join sys.objects)
select N as DateID, DATEADD(d,N,convert(date,'12/31/2015')) as MyDate
,case when DATEPART(dw,DATEADD(d,N,convert(date,'12/31/2015'))) in (1,7) then 'W' else '' end as SpecialDay
into #MyDates
from tally
create unique clustered index PK_#MyDates on #MyDates(MyDate)
-- example of counting days excluding weekends
declare @start date = '5/1/2016'
,@end date = '5/30/2016'
select sum(1) as Workdays
from #MyDates
where Mydate between @start and @end
AND specialday <> 'W'
Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply