May 29, 2015 at 7:21 am
I am trying to generate dates for a roster dynamically in a stored procedure. If my start date is '01/01/2015', end date is '12/31/2015', I should generate dates from '01/01/2015' adding 7 days.
The output comes like this:
01/01/2015
01/08/2015
01/15/2015
.
.
.
until end date
I do it through loop but just posting it here to know, if there is any other efficient way using CTEs or some sort of queries which can avoid loop in SP.
Thanks
May 29, 2015 at 7:27 am
sarath.tata (5/29/2015)
I am trying to generate dates for a roster dynamically in a stored procedure. If my start date is '01/01/2015', end date is '12/31/2015', I should generate dates from '01/01/2015' adding 7 days.The output comes like this:
01/01/2015
01/08/2015
01/15/2015
.
.
.
until end date
I do it through loop but just posting it here to know, if there is any other efficient way using CTEs or some sort of queries which can avoid loop in SP.
Thanks
Kudos for realizing a loop is inefficient and wanting to find a better way!!! Here is an example of using a tally table for this.
declare @StartDate date = '2015-01-01', @EndDate date = '2015-12-31';
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select *
from cteTally
where N <= DATEDIFF(DAY, @StartDate, @EndDate) + 1
AND (N - 1) % 7 = 0
For more details about this technique check out this article from Jeff Moden. http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 7:39 am
The following will easily give you what you want, without resorting to a loop. It basically just applies row_number() across a resultset to get a sequential set of numbers, multiplying by 7, and adding that to a base date. There is nothing special about the master.sys.sysobjects table; anything containing sufficient rows and is guaranteed to exist will do. The WHERE clause can be used to limit the result by date range, exclude specific dates like holidays, etc.
select * from
(
select dateadd(day
,(row_number() over (order by id)-1)*7
,cast('2015/01/01' as date))RosterDate
from master.sys.sysobjects
) as x
where RosterDate <= '2015/12/31';
RosterDate
----------
2015-01-01
2015-01-08
2015-01-15
...
2015-12-17
2015-12-24
2015-12-31
(53 row(s) affected)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 29, 2015 at 7:40 am
I have found a solution. Not so sure, if it is the best solution!!
DECLARE @StartDate DATE = '01/01/2015'
, @EndDate DATE = '01/08/2015'
SET @StartDate = DATEADD(DAY, -7, @StartDate)
;WITH result(tempDate) AS
(
SELECT DATEADD(DAY, nbr, @StartDate)
FROM ( SELECT 7 * ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
FROM sys.all_objects c
) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)
)
SELECT * FROM result r
LEFT OUTER JOIN [tblDutyRosterAvailability] d ON r.tempDate >= @StartDate
ORDER BY r.tempDate, d.DutyRosterCategoryID, d.SortOrder
May 29, 2015 at 7:46 am
Eric M Russell (5/29/2015)
The following will easily give you what you want, without resorting to a loop. It basically just applies row_number() across a resultset to get a sequential set of numbers, multiplying by 7, and adding that to a base date. There is nothing special about the master.sys.sysobjects table; anything containing sufficient rows and is guaranteed to exist will do. The WHERE clause can be used to limit the result by date range, exclude specific dates like holidays, etc.
select * from
(
select dateadd(day
,(row_number() over (order by id)-1)*7
,cast('2015/01/01' as date))RosterDate
from master.sys.sysobjects
) as x
where RosterDate <= '2015/12/31';
RosterDate
----------
2015-01-01
2015-01-08
2015-01-15
...
2015-12-17
2015-12-24
2015-12-31
(53 row(s) affected)
Thanks, I like this!! Looks better than mine!! This is what my final query
;WITH result(RosterDate) AS
(
SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id)) * 7, @StartDate)
FROM sys.all_objects
)
SELECT * FROM result r
LEFT OUTER JOIN [tblDutyRosterAvailability] d ON r.RosterDate >= @StartDate
WHERE RosterDate <= @EndDate
ORDER BY r.RosterDate, d.DutyRosterCategoryID, d.SortOrder
May 29, 2015 at 7:59 am
On my system I have the cteTally as a view so I can generate up to 10,000 rows with zero reads. It is crazy fast and super flexible.
The issue with my original post is I forgot the last step. :blush:
declare @StartDate date = '2015-01-01', @EndDate date = '2015-12-31';
WITH
E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
)
select DATEADD(DAY, N - 1, @StartDate)
from cteTally
where N <= DATEDIFF(DAY, @StartDate, @EndDate)
AND (N - 1) % 7 = 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 8:27 am
Just in case somebody wants
DECLARE @StartDate DATE
DECLARE @EndDate DATE
DECLARE @RotationDays INT
SET @StartDate = '01/01/2015'
SET @EndDate = '12/31/2015'
SET @RotationDays = 4
;WITH result AS
(
SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays, @StartDate) StartDate,
DATEADD(DAY, ((ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays) + @RotationDays - 1, @StartDate) EndDate
FROM sys.all_objects
)
--INSERT INTO TABLE here, if you need to
SELECT StartDate, EndDate
FROM result
--LEFT OUTER JOIN table, if you need to join people here
WHERE EndDate <= @EndDate
May 29, 2015 at 9:43 am
sarath.tata (5/29/2015)
Just in case somebody wantsDECLARE @StartDate DATE
DECLARE @EndDate DATE
DECLARE @RotationDays INT
SET @StartDate = '01/01/2015'
SET @EndDate = '12/31/2015'
SET @RotationDays = 4
;WITH result AS
(
SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays, @StartDate) StartDate,
DATEADD(DAY, ((ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays) + @RotationDays - 1, @StartDate) EndDate
FROM sys.all_objects
)
--INSERT INTO TABLE here, if you need to
SELECT StartDate, EndDate
FROM result
--LEFT OUTER JOIN table, if you need to join people here
WHERE EndDate <= @EndDate
That's actually going to be a bit slow and you do stand the chance of running out of rows because sys.all_objects isn't that big.
The reason why it will be slow is because you're calculating dates for every row there is in sys.all_objects and you probably won't usually need all those rows.
Here's a comparison against the code above and the code that limits the number of rows being generated by the CTE. Look at the actual execution plan and play with SET STATISTICS to see what I mean in the comments.
DECLARE @StartDate DATE
DECLARE @EndDate DATE
DECLARE @RotationDays INT
SET @StartDate = '01/01/2015'
SET @EndDate = '12/31/2015'
SET @RotationDays = 4
--===== Original method generates more than 2000 dates (and 33 reads) rather than just the 91 that are needed.
-- Also has an extra ROW_NUMBER() calculation that's just not needed.
-- In theory, could also run out of "rows" from sys.all_objects.
;WITH result AS
(
SELECT DATEADD(DAY, (ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays, @StartDate) StartDate,
DATEADD(DAY, ((ROW_NUMBER() OVER (ORDER BY object_id) - 1) * @RotationDays) + @RotationDays - 1, @StartDate) EndDate
FROM sys.all_objects
)
--INSERT INTO TABLE here, if you need to
SELECT StartDate, EndDate
FROM result
--LEFT OUTER JOIN table, if you need to join people here
WHERE EndDate <= @EndDate
;
------------------------------------------------------------------------------------------------------
--===== Faster becausse this only generates the 91 rows (and only 5 reads) that are needed
-- and only does one ROW_NUMBER() calcualation.
-- Since the cross join is guaranteed to gen over 16 MILLION rows when needed,
-- there's virtually no chance of ever hitting the proverbial wall.
-- Using a properly formed Tally cte would take the reads down to almost nothing.
WITH
cteStartDates AS
(
SELECT TOP ((DATEDIFF(dd,@StartDate,@EndDate)+1)/@RotationDays)
StartDate = DATEADD(dd,(ROW_NUMBER()OVER(ORDER BY (SELECT NULL))-1)*@RotationDays,@StartDate)
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
)
SELECT StartDate
,EndDate = DATEADD(dd,@RotationDays-1,StartDate)
FROM cteStartDates
WHERE DATEADD(dd,@RotationDays-1,StartDate) <= @EndDate
;
Does such a "small" increase in performance and decrease in resource usage actually make a difference? If you only run it once or twice a day, then not so as anyone would notice. If you run it 40,000 times a day, then the cumulative resource usage starts to be noticed, a lot. I always plan on the larger scale. Like Granny used to say, "Mind the pennies and the dollars will take care of themselves".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply