[dba-SQLServer] Series of Sundays in a query

You're welcome.

I decided on having the number of weeks as a parameter that way if you
need to call it for a different length you can reuse the same udf.

On Tue, Dec 9, 2008 at 5:03 PM, Steve Erbach <erbachs at gmail.com> wrote:
> 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.
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>