December 18, 2014 at 11:58 am
MMartin1 (12/18/2014)
jshuter (12/17/2014)
I understand the need to use SQL when doing SQL but a little looping wont kill ya 🙂I'd still do something like the following
declare @i int = -7
declare @date date
set @date = GETDATE()
while @i < 7 begin
if datepart(weekday, dateadd(D, @i, @Date) ) in (2,3,4,5,6) begin
print dateadd(D, @i, @Date)
end
set @i+=1
end
It does appear that the looping syntax is easier on the eyes and easier to maintain. But the tally table syntax, aside from being a significantly more efficient process, is really not that complicated. Give yourself the opportunity to get familiar with it and it wont appear so daunting
Actually...the looping syntax is much harder on the eyes imho.
I already have a view in my system called cteTally which looks like this.
create View [dbo].[cteTally] as
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 N from cteTally
GO
So if you want to do this on a system where the tally table/view already exists the code can be greatly simplified to something as simple as:
select DATEADD(DAY, 8 - N, GETDATE()) as NewDate
from cteTally
where N <= 15
and DATEPART(WEEKDAY, DATEADD(DAY, 8 - N, GETDATE())) IN (2, 3, 4, 5, 6)
order by N desc
There you have it. A single select statement. No need for variables or looping or anything complicated at all.
_______________________________________________________________
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/
December 18, 2014 at 2:16 pm
Agreed, its not hard on my eyes either. I am trying to get into the mind of someone that has not seen this syntax before. It is not that difficult once you get accustomed to it. I wouldn't want to rely immediately on a view if I want to get practice with applying tally tables directly in code, however. Still that is a fantastic idea that I have made a note of 🙂
----------------------------------------------------
December 19, 2014 at 7:00 am
CELKO (12/19/2014)
Build a calendar table with one column for the calendar data and other columns to show whatever your business needs in the way of temporal information. Do not try to calculate holidays in SQL -- Easter alone requires too much math.
+1
Don Simpson
September 21, 2022 at 11:18 am
This was removed by the editor as SPAM
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply