[dba-SQLServer] Series of Sundays in a query

Best way for this sort of thing is to use a Numbers or Tally Table. If you don't already have one, 
create it on the fly:

-- Drop temporary Numbers table if it exists 
If object_ID('#Numbers') is not null drop table #Numbers
-- Create temporary numbers table with required number of entries
select top 13 identity(Int,1,1) as N
into #Numbers from master.dbo.syscolumns
-- Create list or WeekBeginnings
select DATEADD(day, (N * 7 + 1) - DATEPART(weekday, GETDATE()), 
GETDATE()) AS WeekBeginning from #Numbers
-- Drop temporary numbers table
drop table #Numbers


On 9 Dec 2008 at 16:56, Steve Erbach wrote:

> Dear Group,
> 
> In an ASP.NET application I'm writing for work, I'd like to create a
> work scheduling web form. One section contains a check box list of all
> employees, another is a radio button list of the different possible
> shifts to choose from, and a third is a checkbox list of "week
> beginning" dates.  The Supervisor checks off all the employees for a
> particular shift, selects the shift, and then selects the weeks that
> those shifts apply.
> 
> Anyway, I want to display 13 weeks of "week beginning dates like so:
> 
> _ 12/14/2008
> _ 12/21/2008
> _ 12/28/2008
> _ 1/4/2009
> etc.
> 
> I have a table in which each row represents the starting date of each
> week for the coming year, but I thought that there might be a SQL
> query that I could create to generate 13-weeks' worth of
> week-beginning dates, starting with the Sunday immediately coming up.
> 
> I searched through Joe Celko's "SQL for Smarties", reading the chapter
> on temporal data in SQL, but I didn't see anything suitable.
> 
> Any ideas?  The brute force approach is a 13-query UNION something like this:
> 
> SELECT DATEADD(day, 8 - DATEPART(weekday, GETDATE()), GETDATE()) AS
> WeekBeginning
> UNION
> SELECT DATEADD(day, 15 - DATEPART(weekday, GETDATE()), GETDATE()) AS
> WeekBeginning
> UNION
> SELECT DATEADD(day, 22 - DATEPART(weekday, GETDATE()), GETDATE()) AS
> WeekBeginning
> UNION
> etc.
> 
> ...but this seems TOO brute-forcish.
> 
> Regards,
> 
> Steve Erbach
> Neenah, WI
> http://www.TheTownCrank.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>