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/