[dba-SQLServer] Series of Sundays in a query

David,

Excellent!  I'd thought of possibly making a UDF; just never followed
through, but that makes perfect sense.  Thank you.

Steve Erbach

On Tue, Dec 9, 2008 at 5:56 PM, David McAfee <davidmcafee at gmail.com> wrote:
> I don't know if is any better, but you can create a udf such as:
>
> CREATE FUNCTION udfWeekBegins (@StartDate AS DATETIME, @NumOfWks AS
> INT) RETURNS @ListTable TABLE (WeekBeginning DATETIME) AS
> BEGIN
>    DECLARE @LoopDate AS DATETIME SET @LoopDate = @StartDate
>    WHILE @LoopDate < DATEADD(wk, at NumOfWks, at StartDate)
>        BEGIN
>            INSERT INTO @ListTable(WeekBeginning) SELECT @LoopDate
>            SET @LoopDate = DATEADD(dd,7, at LoopDate)
>        END
>    RETURN
> END
>
>
>
> Then call it as such:
> SELECT WeekBeginning FROM dbo.udfWeekBegins('12/08/2008',13) --This
> returns the next 13 weeks from the date entered, not the next 13
> Sundays
>
> or like this:
> DECLARE @StartWeek AS DATETIME SET @StartWeek =  DATEADD(day, 8 -
> DATEPART(weekday, GETDATE()), GETDATE())
> SELECT * FROM dbo.udfWeekBegins ( @StartWeek, 13)
>
> ResultSet:
> 2008-12-14 15:55:06.943
> 2008-12-21 15:55:06.943
> 2008-12-28 15:55:06.943
> 2009-01-04 15:55:06.943
> 2009-01-11 15:55:06.943
> 2009-01-18 15:55:06.943
> 2009-01-25 15:55:06.943
> 2009-02-01 15:55:06.943
> 2009-02-08 15:55:06.943
> 2009-02-15 15:55:06.943
> 2009-02-22 15:55:06.943
> 2009-03-01 15:55:06.943
> 2009-03-08 15:55:06.943
>
>
>
>
> On Tue, Dec 9, 2008 at 2:56 PM, Steve Erbach <erbachs at gmail.com> 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.