January 8, 2016 at 4:06 pm
Hi Guys,
Looking for a cte which will list all dates between 2 dates 7 days apart. May want to change to 14 days apart later.
Here is my code....but gives me all dates...
BEGIN
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),
cteDates(calDate) AS(
SELECT TOP(DATEDIFF( dd, '2010-01-01', '2010-03-01') + 7) --Create all the dates needed with the use of a tally table
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 7, '2010-01-01') calDate
FROM E4
)
SELECT * FROM cteDates;
END
January 8, 2016 at 4:23 pm
Tallboy (1/8/2016)
Hi Guys,Looking for a cte which will list all dates between 2 dates 7 days apart. May want to change to 14 days apart later.
Here is my code....but gives me all dates...
BEGIN
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),
cteDates(calDate) AS(
SELECT TOP(DATEDIFF( dd, '2010-01-01', '2010-03-01') + 7) --Create all the dates needed with the use of a tally table
DATEADD( dd, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 7, '2010-01-01') calDate
FROM E4
)
SELECT * FROM cteDates;
END
Doesn't help much. First, what is your date format (mdy or dmy). What are your inputs and what is expected to be returned. Sorry, but I am a visual style problem solver and the code you posted doesn't match the problem you stated.
January 8, 2016 at 4:42 pm
I didn't run your code, but if it gives you all dates and you need only those between two dates, then you probably can just add WHERE calDate BETWEEN @StartDate AND @EndDate at the end of your query.
That being said, the whole construction appears to be pretty complex and slow. My prefered option would be to create a permanent calendar table and simply select from that. Second best if you have a tally table is to use SELECT DATEADD(day, n - 1, @StartDate) FROM Tally WHERE n BETWEEN 1 AND 7
January 9, 2016 at 12:54 am
Quick solution
😎
USE tempdb;
GO
SET NOCOUNT ON;
DECLARE @START_DATE DATE = CONVERT(DATE,'20100101',112);
DECLARE @END_DATE DATE = CONVERT(DATE,'20100901',112);
DECLARE @INTERVAL INT = 14;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
, NUMS(N) AS (SELECT TOP((DATEDIFF(DAY,@START_DATE,@END_DATE) / @INTERVAL ) +1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS N
FROM T T1,T T2,T T3,T T4)
SELECT
NM.N
,DATEADD(DAY,(NM.N * @INTERVAL),@START_DATE) AS OUT_DATE
FROM NUMS NM ;
Output
N OUT_DATE
----- ----------
0 2010-01-01
1 2010-01-15
2 2010-01-29
3 2010-02-12
4 2010-02-26
5 2010-03-12
6 2010-03-26
7 2010-04-09
8 2010-04-23
9 2010-05-07
10 2010-05-21
11 2010-06-04
12 2010-06-18
13 2010-07-02
14 2010-07-16
15 2010-07-30
16 2010-08-13
17 2010-08-27
January 9, 2016 at 7:21 am
Hi Guys, thank you so muchagain.
Apologies to Lynn I thought all ms sql server databases had default dates as 'yyyy-mm-dd', also I should have mentioned there are only 2 inputs startdate and end date!
Hugo I am using a calendar but this requires maintenance and increase the size of the database unnecessarily and I want to see if it can work without a calendar table ir that the sp be self contained!
So big thanks to Eirikur, this example looks good and I can change the interval to 7 days if need be!
kind regards to all. SQL CENTRAL ROCKS! :w00t::w00t::w00t:
January 9, 2016 at 9:11 am
You are very welcome and thanks for the feedback.
😎
As demonstrated here, often in-line date tables are easier solutions than physical date tables, especially if they are supporting logic rather than schema objects and constraints, the drawbacks are the cardinality estimates being way off if used in table value functions etc.
Generating a ten years worth of dates, 3654 entries, is so quick that it is hard to actually time it, a century's worth takes milliseconds and all days since 0001-01-01 (735964) will take less than 1/5 of a second an old laptop like mine.
January 9, 2016 at 9:27 am
Eirikur Eiriksson (1/9/2016)
You are very welcome and thanks for the feedback.😎
As demonstrated here, often in-line date tables are easier solutions than physical date tables, especially if they are supporting logic rather than schema objects and constraints, the drawbacks are the cardinality estimates being way off if used in table value functions etc.
Generating a ten years worth of dates, 3654 entries, is so quick that it is hard to actually time it, a century's worth takes milliseconds and all days since 0001-01-01 (735964) will take less than 1/5 of a second an old laptop like mine.
On the other hand, if used multiple times having a permanent calendar table removes a lot of duplicated logic. And it takes almost no space - ten years of data takes only about 10K on disk if only the date is stored (more if you add extra columns, but then you also get extra options)
Not saying that one is better than the other, just presenting some extra arguments to consider when making a choice.
January 9, 2016 at 9:35 am
Hugo Kornelis (1/9/2016)
Eirikur Eiriksson (1/9/2016)
You are very welcome and thanks for the feedback.😎
As demonstrated here, often in-line date tables are easier solutions than physical date tables, especially if they are supporting logic rather than schema objects and constraints, the drawbacks are the cardinality estimates being way off if used in table value functions etc.
Generating a ten years worth of dates, 3654 entries, is so quick that it is hard to actually time it, a century's worth takes milliseconds and all days since 0001-01-01 (735964) will take less than 1/5 of a second an old laptop like mine.
On the other hand, if used multiple times having a permanent calendar table removes a lot of duplicated logic. And it takes almost no space - ten years of data takes only about 10K on disk if only the date is stored (more if you add extra columns, but then you also get extra options)
Not saying that one is better than the other, just presenting some extra arguments to consider when making a choice.
Totally agree Hugo, probably the best way of putting it is that common sense applies. If the logic can be encapsulated in the actual date table and it is used more than once then a physical date table is almost always a better option. My point is that one can apply set based date logic without necessarily having a physical date table, forgot to say that it applies where applicable.
😎
January 9, 2016 at 10:15 pm
Eirikur Eiriksson (1/9/2016)
If the logic can be encapsulated in the actual date table and it is used more than once then a physical date table is almost always a better option.
I'd have to say "It Depends". For example, it's been proven that a Tally Table will operate with less CPU time and duration than Itzik's wonderful cCTE method, but it comes at the cost of logical reads (memory I/O) which, contrary to popular belief, does have a limit and can become a bottle neck. The same holds true for a Calendar Table.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2016 at 10:49 pm
Jeff Moden (1/9/2016)
Eirikur Eiriksson (1/9/2016)
If the logic can be encapsulated in the actual date table and it is used more than once then a physical date table is almost always a better option.I'd have to say "It Depends". For example, it's been proven that a Tally Table will operate with less CPU time and duration than Itzik's wonderful cCTE method, but it comes at the cost of logical reads (memory I/O) which, contrary to popular belief, does have a limit and can become a bottle neck. The same holds true for a Calendar Table.
It is true that "it depends" or as I put it earlier, "common sense applies". The scenarios I'm referring to would typically be the likes of national holidays, irregular periods etc. which are hard or even impossible to calculate. On the other hand, in a data warehouse where the data is keyed on the dates then an inline date table wouldn't make too much sense either. The point is that inline date tables are easy to implement and often will greatly reduce date type problems complexities, should be everyone's tool box.
😎
January 10, 2016 at 10:21 am
Eirikur Eiriksson (1/9/2016)
Jeff Moden (1/9/2016)
Eirikur Eiriksson (1/9/2016)
If the logic can be encapsulated in the actual date table and it is used more than once then a physical date table is almost always a better option.I'd have to say "It Depends". For example, it's been proven that a Tally Table will operate with less CPU time and duration than Itzik's wonderful cCTE method, but it comes at the cost of logical reads (memory I/O) which, contrary to popular belief, does have a limit and can become a bottle neck. The same holds true for a Calendar Table.
It is true that "it depends" or as I put it earlier, "common sense applies". The scenarios I'm referring to would typically be the likes of national holidays, irregular periods etc. which are hard or even impossible to calculate. On the other hand, in a data warehouse where the data is keyed on the dates then an inline date table wouldn't make too much sense either. The point is that inline date tables are easy to implement and often will greatly reduce date type problems complexities, should be everyone's tool box.
😎
Agreed on all points. I just hate (kind of a sore spot with me, really) to see Calendar tables that contain a bazillion columns for things that will never be used as a lookup or a normal temporal function in code will do and so had to say something about it. Apologies for the short rants that aren't explaining the big picture causing the rants. 🙂
On that note, another rant. 😀 I hate it when people assign an ID column in a Calendar Table and then use that ID in other tables instead of actual dates even when it represented a 4 byte storage savings before the DATE datatype came out (heh... which I also hate... not because of what it is but because of when people store date and time separately on the same row).
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply