Professional Web Applications Themes

Trying to get the last 10 Sundays by date - Microsoft SQL / MS SQL Server

jack wrote:  One way would be to create a calendar table: CREATE TABLE Calendar ( CalDate datetime, PRIMARY KEY, DayOfWk tinyint) go declare start datetime Set start='20030101' WHILE start < '20050101' BEGIN insert into Calendar values (start,datepart(dw,start) Set start = dateadd(day,1,start) END WHILE Now it's simply a matter of: Select Top 10 CalDate FROM Calendar WHERE CalDate < GetDate() AND DayOfWk = 1 ORDER BY CalDate DESC The Calendar table should have other uses once you start to think about it .... Bob Barrows...

  1. #1

    Default Re: Trying to get the last 10 Sundays by date

    jack wrote: 
    One way would be to create a calendar table:

    CREATE TABLE Calendar (
    CalDate datetime, PRIMARY KEY,
    DayOfWk tinyint)
    go
    declare start datetime
    Set start='20030101'
    WHILE start < '20050101'
    BEGIN
    insert into Calendar
    values (start,datepart(dw,start)
    Set start = dateadd(day,1,start)
    END
    WHILE

    Now it's simply a matter of:
    Select Top 10 CalDate FROM Calendar
    WHERE CalDate < GetDate() AND
    DayOfWk = 1
    ORDER BY CalDate DESC

    The Calendar table should have other uses once you start to think about it
    ....

    Bob Barrows


    Bob Guest

  2. #2

    Default Re: Trying to get the last 10 Sundays by date

    "jack" <com> wrote in message news:phx.gbl... 

    Here's a UDF that, from a given reference date, returns either
    the next, or previous, |n| dates that fall on a given day of the week
    d. The day of the week d is specified by an integer from 1 to 7
    where Sunday is 1, Monday is 2, and so on. The value n gives
    both the direction and magnitude of the dates needed, i.e., if n
    is positive then return n forward dates and if n is negative then
    return |n| backward dates. For example, if n is 5 and d is 3, then
    return the next 5 Tuesdays with respect to the reference date.
    If n is -10 and d is 1, then return the previous 10 Sundays with
    respect to a given reference date.

    CREATE FUNCTION GetNDates (ref_date DATETIME,
    day INT,
    n INT)
    RETURNS dates TABLE
    (d DATETIME NOT NULL PRIMARY KEY)
    AS
    BEGIN
    IF day BETWEEN 1 AND 7 AND n <> 0
    BEGIN
    DECLARE direction INT,
    wd INT,
    date DATETIME,
    counter INT
    SELECT ref_date = CONVERT(CHAR(8), ref_date, 112),
    direction = SIGN(n),
    n = direction * n,
    wd = DATEPART(WEEKDAY, ref_date),
    date = ref_date +
    direction * (((direction * (day - wd)) + 7) % 7),
    counter = 1
    INSERT INTO dates (d)
    VALUES (date)
    WHILE counter < n
    BEGIN
    SELECT date = date + 7 * direction,
    counter = counter + 1
    INSERT INTO dates (d)
    VALUES (date)
    END
    END
    RETURN
    END

    CREATE VIEW CurrentTimestamp (dt)
    AS
    SELECT CURRENT_TIMESTAMP

    -- A helper function for your particular problem
    CREATE FUNCTION GetPrevious10Sundays
    (ref_date DATETIME = NULL)
    RETURNS dates TABLE
    (d DATETIME NOT NULL PRIMARY KEY)
    AS
    BEGIN
    IF ref_date IS NULL
    SELECT ref_date = dt
    FROM CurrentTimestamp
    INSERT INTO dates (d)
    SELECT d FROM GetNDates(ref_date, 1, -10)
    RETURN
    END

    SELECT d
    FROM GetPrevious10Sundays(DEFAULT) -- ref date is today, 20030803
    ORDER BY d DESC

    d
    2003-08-03 00:00:00.000
    2003-07-27 00:00:00.000
    2003-07-20 00:00:00.000
    2003-07-13 00:00:00.000
    2003-07-06 00:00:00.000
    2003-06-29 00:00:00.000
    2003-06-22 00:00:00.000
    2003-06-15 00:00:00.000
    2003-06-08 00:00:00.000
    2003-06-01 00:00:00.000

    SELECT d
    FROM GetPrevious10Sundays('20030802') -- ref date is 20030802
    ORDER BY d DESC

    d
    2003-07-27 00:00:00.000
    2003-07-20 00:00:00.000
    2003-07-13 00:00:00.000
    2003-07-06 00:00:00.000
    2003-06-29 00:00:00.000
    2003-06-22 00:00:00.000
    2003-06-15 00:00:00.000
    2003-06-08 00:00:00.000
    2003-06-01 00:00:00.000
    2003-05-25 00:00:00.000

    Regards,
    jag


    John Guest

  3. #3

    Default Re: Trying to get the last 10 Sundays by date

    you might find this script useful:

    --SET DATEFIRST 1 -- script is invariant to datefirst connection
    setting
    --SET DATEFIRST 7

    DECLARE NumDates INT
    , StartDate DATETIME

    SELECT NumDates = -10
    , StartDate = GETDATE()

    SELECT s.Date
    FROM (
    SELECT StartDate + num.Seq * SIGN(NumDates) AS Date
    FROM (
    SELECT COUNT(*)-1 AS Seq
    FROM sysobjects s1
    JOIN sysobjects s2
    ON s2.ID <= s1.ID
    GROUP BY s1.ID
    ) num
    WHERE num.Seq <= ABS(NumDates)*7-1
    ) s
    WHERE DATEPART(dw, s.Date + DATEFIRST) = 1
    ORDER BY s.Date

    HTH,
    </wqw>

    "jack" <com> wrote in message
    news:phx.gbl... 


    Vlad Guest

  4. #4

    Default Re: Trying to get the last 10 Sundays by date

    Hello,

    Do you know I can get the ten dates into varables like week1, week2,
    week3....week10?

    Thanks for all you help,

    Jack
    "Vlad Vissoultchev" <myrealbox.com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    jack Guest

  5. #5

    Default Re: Trying to get the last 10 Sundays by date

    "jack" <com> wrote in message news:phx.gbl... 

    DECLARE week1 DATETIME,
    week2 DATETIME,
    week3 DATETIME,
    week4 DATETIME,
    week5 DATETIME,
    week6 DATETIME,
    week7 DATETIME,
    week8 DATETIME,
    week9 DATETIME,
    week10 DATETIME
    DECLARE OrderedDates TABLE
    (d DATETIME NOT NULL,
    rank INT NOT NULL PRIMARY KEY)
    INSERT INTO OrderedDates (d, rank)
    SELECT S1.d, COUNT(S2.d)
    FROM GetPrevious10Sundays(DEFAULT) AS S1
    INNER JOIN
    GetPrevious10Sundays(DEFAULT) AS S2
    ON S2.d >= S1.d
    GROUP BY S1.d

    SELECT week1 = CASE WHEN rank = 1 THEN d ELSE week1 END,
    week2 = CASE WHEN rank = 2 THEN d ELSE week2 END,
    week3 = CASE WHEN rank = 3 THEN d ELSE week3 END,
    week4 = CASE WHEN rank = 4 THEN d ELSE week4 END,
    week5 = CASE WHEN rank = 5 THEN d ELSE week5 END,
    week6 = CASE WHEN rank = 6 THEN d ELSE week6 END,
    week7 = CASE WHEN rank = 7 THEN d ELSE week7 END,
    week8 = CASE WHEN rank = 8 THEN d ELSE week8 END,
    week9 = CASE WHEN rank = 9 THEN d ELSE week9 END,
    week10 = CASE WHEN rank = 10 THEN d ELSE week10 END
    FROM OrderedDates

    Regards,
    jag
     
    > news:phx.gbl... 
    > >
    > > Here's a UDF that, from a given reference date, returns either
    > > the next, or previous, |n| dates that fall on a given day of the week
    > > d. The day of the week d is specified by an integer from 1 to 7
    > > where Sunday is 1, Monday is 2, and so on. The value n gives
    > > both the direction and magnitude of the dates needed, i.e., if n
    > > is positive then return n forward dates and if n is negative then
    > > return |n| backward dates. For example, if n is 5 and d is 3, then
    > > return the next 5 Tuesdays with respect to the reference date.
    > > If n is -10 and d is 1, then return the previous 10 Sundays with
    > > respect to a given reference date.
    > >
    > > CREATE FUNCTION GetNDates (ref_date DATETIME,
    > > day INT,
    > > n INT)
    > > RETURNS dates TABLE
    > > (d DATETIME NOT NULL PRIMARY KEY)
    > > AS
    > > BEGIN
    > > IF day BETWEEN 1 AND 7 AND n <> 0
    > > BEGIN
    > > DECLARE direction INT,
    > > wd INT,
    > > date DATETIME,
    > > counter INT
    > > SELECT ref_date = CONVERT(CHAR(8), ref_date, 112),
    > > direction = SIGN(n),
    > > n = direction * n,
    > > wd = DATEPART(WEEKDAY, ref_date),
    > > date = ref_date +
    > > direction * (((direction * (day - wd)) +[/ref]
    > 7) % 7), 
    >
    >[/ref]


    John Guest

  6. #6

    Default Re: Trying to get the last 10 Sundays by date


    "jack" <com> wrote in message
    news:phx.gbl... 

    declare somedate datetime
    declare sunday0 datetime
    declare sunday1 datetime
    declare sunday2 datetime
    declare sunday3 datetime
    declare sunday4 datetime
    declare sunday5 datetime
    declare sunday6 datetime
    declare sunday7 datetime
    declare sunday8 datetime
    declare sunday9 datetime

    set somedate = '8/3/2003'

    -- How about finding the first Sunday with something, like say:
    set sunday0 = dateadd(
    day,
    1 - (datefirst + datepart(weekday,somedate)) % 7,
    somedate
    )

    -- and then use that one to get the rest of the Sundays
    -- with something like
    set sunday1 = dateadd(day,-7,sunday0)
    set sunday2 = dateadd(day,-7,sunday1)
    set sunday3 = dateadd(day,-7,sunday2)
    set sunday4 = dateadd(day,-7,sunday3)
    set sunday5 = dateadd(day,-7,sunday4)
    set sunday6 = dateadd(day,-7,sunday5)
    set sunday7 = dateadd(day,-7,sunday6)
    set sunday8 = dateadd(day,-7,sunday7)
    set sunday9 = dateadd(day,-7,sunday8)
    select
    sunday0,sunday1,sunday2,sunday3,sunday4,
    sunday5,sunday6,sunday7,sunday8,sunday9

    -- or
    set sunday1 = dateadd(day,-7*1,sunday0)
    set sunday2 = dateadd(day,-7*2,sunday0)
    set sunday3 = dateadd(day,-7*3,sunday0)
    set sunday4 = dateadd(day,-7*4,sunday0)
    set sunday5 = dateadd(day,-7*5,sunday0)
    set sunday6 = dateadd(day,-7*6,sunday0)
    set sunday7 = dateadd(day,-7*7,sunday0)
    set sunday8 = dateadd(day,-7*8,sunday0)
    set sunday9 = dateadd(day,-7*9,sunday0)
    select
    sunday0,sunday1,sunday2,sunday3,sunday4,
    sunday5,sunday6,sunday7,sunday8,sunday9

    -- etc.

    Bye,
    Delbert Glass


    Delbert Guest

Similar Threads

  1. JSObject returns wrong date. How can Iextract correct date from digital signature?
    By Hal_Underwood@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: February 19th, 01:22 AM
  2. #40937 [NEW]: date() lose Sundays
    By ctrlaltca in forum PHP Bugs
    Replies: 10
    Last Post: April 16th, 02:27 PM
  3. Replies: 1
    Last Post: October 24th, 11:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139