How many more Mondays until I retire?

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Comments posted to this topic are about the item How many more Mondays until I retire?


    N 56°04'39.16"
    E 12°55'05.25"

  • Ally MacKenzie

    Right there with Babe

    Points: 779

    Nice article,  alternatively...

    SET DATEFIRST 1 -- force monday as the beginning of the week

    DECLARE @bdy datetime,

     @RetiralAge smallint

    SET @bdy='24-jul-1971'

    SET @RetiralAge=65

    SELECT  datediff (dd,getdate()+(8-datepart(dw,getdate())),dateadd(year,@RetiralAge,@bdy))/7  as "Mondays till you retire"

    -Ally

  • SwePeso

    SSC-Dedicated

    Points: 39693

    True, and it works too. But maybe not that versatile?


    N 56°04'39.16"
    E 12°55'05.25"

  • Jesper-244176

    SSCertifiable

    Points: 7032

    Calculating the number of Mondays between two dates was the subject of the following thread:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=258968

    My suggestion was the following:

    select datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

    I still believe this is the fastest method to calculate the number of Mondays between two dates. On the other hand, it is not very flexible...

     

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Yes, it works well if not more than one of the two dates already is a monday. Using this code

    select (datediff(d, '20060213', '20060724') + 1)/7 - datediff(d, '20060213', '20060717')/7

    reports 1 monday to me, where it should report 2 mondays.

    But your query is very fast.


    N 56°04'39.16"
    E 12°55'05.25"

  • Jesper-244176

    SSCertifiable

    Points: 7032

    No, no, use the formula above:

    select datediff(d, '19000101', '20060724')/7 - datediff(d, '19000102', '20060717')/7

    returns 2, i.e 2 Mondays between the 17th and the 24th.

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Ok, ok ok

    Still don't get proper result with date range 1899-12-29 and 1900-01-03.

    Running

    select datediff(d, '19000101', '1900-01-03')/7 - datediff(d, '19000102', '1899-12-29')/7

    gives me 0 mondays. Shouldn't it report 1 monday for January 1, 1900?


    N 56°04'39.16"
    E 12°55'05.25"

  • Jesper-244176

    SSCertifiable

    Points: 7032

    Do you really need dates that old?

    You could use

    select datediff(d, '18000106', @dateTo)/7 - datediff(d, '18000107', @dateFrom)/7

    instead. Note that

    select datediff(d, '18000106', '1900-01-03')/7 - datediff(d, '18000107', '1899-12-29')/7

    gives you one Monday.

    My formula needs two reference dates, a Monday (e.g. 1900-01-01 or 1800-01-06) and the following Tuesday (e.g. 1900-01-02 or 1800-01-07). Both @dateFrom and @dateTo must be larger than these days for the formula to work. But OK, you found the weak point

     

  • Razvan Socol

    SSCarpal Tunnel

    Points: 4738

    In the first code sample, I suppose that this portion should return each number between 0 and 32, right ?

    SELECT

    a.i + b.j + c.k

    FROM

     (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2) a,

     (SELECT 0 j UNION ALL SELECT 3 UNION ALL SELECT 6) b,

     (SELECT 0 k UNION ALL SELECT 12 UNION ALL SELECT 24) c

    ORDER BY 1

    Well... it doesn't. It generate some numbers between 0 and 32, but not all of them. The above query only generates 27 values. If you want to generate each number between 0 and 31, you can use this query:

    SELECT a.i + b.j + c.k

    FROM

     (SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) a,

     (SELECT 0 j UNION ALL SELECT 4 UNION ALL SELECT 8 UNION ALL SELECT 12) b,

     (SELECT 0 k UNION ALL SELECT 16) c

    ORDER BY 1

    Razvan

  • Razvan Socol

    SSCarpal Tunnel

    Points: 4738

    I modified your function as follows:

    CREATE

    FUNCTION dbo.MySeqDates (@LowDate DATETIME, @HighDate DATETIME)

    RETURNS @Dates TABLE (SeqDate DATETIME)

    AS

    BEGIN

    DECLARE @Temp DATETIME

    IF @LowDate > @HighDate

     SELECT @Temp = @LowDate,

      @LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0),

      @HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)

     ELSE

     SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0),

      @HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)

    INSERT @Dates (SeqDate) VALUES (@LowDate)

    DECLARE @TotalRows int, @RowCnt int

    SET @TotalRows=1

    SET @RowCnt=1

    WHILE @RowCnt > 0 BEGIN

     INSERT @Dates (SeqDate)

     SELECT DATEADD(dd, @TotalRows, d.SeqDate)

     FROM @Dates d

     WHERE DATEADD(dd, @TotalRows, d.SeqDate) <= @HighDate

     SET @RowCnt=@@ROWCOUNT

     SET @TotalRows=@TotalRows+@RowCnt

    END

    RETURN

    END

    The difference is that I use a variable to count the number of rows (instead of using COUNT() in a derived table) which should be faster if the number of rows is really big. To benchmark, try running something like this:

    DECLARE

    @t datetime

    SET @t=GETDATE()

    SELECT COUNT(*) FROM dbo.fnSeqDates('20060101','23070131')

    PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'

    SET @t=GETDATE()

    SELECT COUNT(*) FROM dbo.MySeqDates('20060101','23070131')

    PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'

    Of course, it's very unlikely that anyone would need such a long period (and for shorter periods the difference in performance is very small).

    Razvan

  • John Rempel

    SSCommitted

    Points: 1795

    Well done Peter. I like the versatility. Great examples.

    You could even populate the holiday table this way, at least when they are defined like the Civic Holiday or Labour Day (in Canada) as the first Monday in August and September, respectively. For holidays dependant on the lunar cycle, like Easter... whatcha gonna do?

  • SwePeso

    SSC-Dedicated

    Points: 39693

    The best way would be to use this function

    CREATE FUNCTION dbo.GetEasterSunday 

        @Y INT 

    RETURNS SMALLDATETIME 

    AS 

    BEGIN 

        DECLARE     @EpactCalc INT,  

                    @PaschalDaysCalc INT, 

                    @NumOfDaysToSunday INT, 

                    @EasterMonth INT, 

                    @EasterDay INT 

     

        SET @EpactCalc = (24 + 19 * (@Y % 19)) % 30 

        SET @PaschalDaysCalc = @EpactCalc - (@EpactCalc / 28) 

        SET @NumOfDaysToSunday = @PaschalDaysCalc - ((@Y + @Y / 4 + @PaschalDaysCalc - 13) % 7)

        SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40) / 44

        SET @EasterDay = @NumOfDaysToSunday + 28 - (31 * (@EasterMonth / 4)) 

     

        RETURN 

        ( 

            SELECT CONVERT 

            ( 

                SMALLDATETIME, 

     

                RTRIM(@Y)  

                + RIGHT('0'+RTRIM(@EasterMonth), 2)  

                + RIGHT('0'+RTRIM(@EasterDay), 2) 

            ) 

        ) 

    END 

    Taken from http://www.aspfaq.com/show.asp?id=2519


    N 56°04'39.16"
    E 12°55'05.25"

  • SwePeso

    SSC-Dedicated

    Points: 39693

    Yes, I did benchmark your code Razvan. Did you try your test-code yourself?

    Running your test-code above gives 763 milliseconds for my function and 1,563 milliseconds for your function.

    Mine is faster and I think that is because SQL is not very fast handling two variables as in your function.


    N 56°04'39.16"
    E 12°55'05.25"

  • Richard Thomas-189729

    Grasshopper

    Points: 22

    I had the idea of populating the Holiday table myself this morning. I found a function to calculate the date for Easter on this page:

    http://www.databasejournal.com/scripts/article.php/3469911

    Then I used that along with Peter's seqDates function to create the following SP. Covers all UK Holidays, and if New Year, Christmas or Boxing Day occur at the weekend it inserts the appropriate Monday/Tuesday into the table.

    CREATE PROCEDURE GenerateHolidays (@yr SMALLINT)

    AS

    DECLARE @tmpdatec VARCHAR(10)

    DECLARE @tmpdate SMALLDATETIME

    DECLARE @tmpdesc VARCHAR(50)

    DECLARE @sdate   VARCHAR(10)

    DECLARE @edate   VARCHAR(10)

    SET @tmpdatec = '01/01/' + CAST(@yr AS VARCHAR)

    SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)

    IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)

    IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)

    SET @tmpdesc = 'New Year''s Day'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

    SELECT @tmpdate = dbo.fnYear2Easter(@yr)

    SELECT @tmpdate = DATEADD(DAY, -2, @tmpdate)

    SELECT @tmpdesc = 'Good Friday'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

    SELECT @tmpdate = DATEADD(DAY, 3, @tmpdate)

    SELECT @tmpdesc = 'Easter Monday'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

    SET @sdate = '05/01/' + CAST(@yr AS VARCHAR)

    SET @edate = '05/31/' + CAST(@yr AS VARCHAR)

    SELECT @tmpdate = MIN(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2

    SET @tmpdesc = 'May Day Bank Holiday'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

    SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2

    SET @tmpdesc = 'Spring Bank Holiday'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

    SET @sdate = '08/01/' + CAST(@yr AS VARCHAR)

    SET @edate = '08/31/' + CAST(@yr AS VARCHAR)

    SELECT @tmpdate = MAX(dt.SeqDate) FROM dbo.fnSeqDates(@sdate, @edate) dt WHERE DATEPART(dw, dt.SeqDate) = 2

    SET @tmpdesc = 'August Bank Holiday'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

    SET @tmpdatec = '12/25/' + CAST(@yr AS VARCHAR)

    SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)

    IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)

    IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 1, @tmpdate)

    SET @tmpdesc = 'Christmas Day'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

    SET @tmpdatec = '12/26/' + CAST(@yr AS VARCHAR)

    SET @tmpdate = CAST(@tmpdatec AS SMALLDATETIME)

    IF DATEPART(dw, @tmpdate) = 7 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate)

    IF DATEPART(dw, @tmpdate) = 1 SELECT @tmpdate = DATEADD(DAY, 2, @tmpdate)

    SET @tmpdesc = 'Boxing Day'

    INSERT INTO Holidays VALUES (@tmpdate, @tmpdesc)

     

  • R2ro

    Default port

    Points: 1409

    I too wrote a slightly different version...  Same binary concept, implemented slightly differently with a twist!  It allows the specification of an increment or step value.

    CREATE FUNCTION dbo.udf_genDateSequence    (       @prmLoDate datetime, -- lower date boundary (starting value)       @prmHiDate datetime, -- upper date boundary (maximum value)       @prmIncDays int      -- increment value    ) RETURNS @Dates TABLE (DateVal datetime NOT NULL PRIMARY KEY) AS /*

    Function:   Generate a table of dates.

    Strategy:   Set-based scalar value generation.

    Usage:      dbo.udf_genDateSequence(fromValue, toValue, stepValue)

    */

    BEGIN    DECLARE       @daysDiff int,       @swapVar datetime

     

       IF @prmLoDate > @prmHiDate          BEGIN             SET @swapVar = @prmLoDate             SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)             SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @swapVar), 0)          END       ELSE          BEGIN             SET @prmLoDate = DATEADD(day, DATEDIFF(day, 0, @prmLoDate), 0)             SET @prmHiDate = DATEADD(day, DATEDIFF(day, 0, @prmHiDate), 0)          END

     

       SET @daysDiff = DATEDIFF(day, @prmLoDate, @prmHiDate)

     

       INSERT INTO @Dates VALUES(@prmLoDate)

     

       WHILE @prmIncDays <= @daysDiff       BEGIN          INSERT INTO @Dates             SELECT DATEADD(day, @prmIncDays, DateVal)                FROM @Dates                WHERE DateVal <= DATEADD(day, -@prmIncDays, @prmHiDate)

     

             SET @prmIncDays = @prmIncDays * 2       END

       RETURN END go

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply