[dba-SQLServer] Series of Sundays in a query

Stuart,

Boy!  I had no idea there'd be so many ways to do this: my brute force
UNION query, David's UDF, Asger's loop with a table variable, and your
temporary Numbers tale.  Thank you very much.  This has been MOST
educational.

Steve Erbach
Neenah, WI

On Tue, Dec 9, 2008 at 7:40 PM, Stuart McLachlan <stuart at lexacorp.com.pg> wrote:
> 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.