[dba-SQLServer] Series of Sundays in a query

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.
>
> 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
>