Need the First Tuesday Beyond Y days after X Date

  • Comments posted to this topic are about the item Need the First Tuesday Beyond Y days after X Date

  • Hi,

    Just sorted out after some hindrance that the parentheses are not in the right order for the supplied script (last line). Below is the changed script. Cheers.

    DECLARE @StartDate date

    DECLARE @DaysOut tinyint

    DECLARE @DesiredDayOfWeek tinyint

    SET @StartDate = '20110813' --The starting date

    SET @DaysOut = 4 --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6 --The desired day of week

    SELECT DATEADD( d,( @DaysOut +( 7 - ( ( 7 + DATEPART( weekday, DATEADD( d, @DaysOut, @StartDate )) - @DesiredDayOfWeek ) % 7) )), @StartDate )

  • So which is really better - the very clever one-liner whose logic can't be unravelled without some head scratching, or the somewhat longer version that's simple enough to be self-explanatory?

    [font="Courier New"]select @TargetDate = dateadd(dd,@DaysOut,@StartDate)

    while datepart(dw,@TargetDate) <> @DesiredDayOfWeek

    set @TargetDate = @TargetDate + 1[/font]

  • Bob-683340 (10/13/2011)


    So which is really better - the very clever one-liner whose logic can't be unravelled without some head scratching, or the somewhat longer version that's simple enough to be self-explanatory?

    [font="Courier New"]select @TargetDate = dateadd(dd,@DaysOut,@StartDate)

    while datepart(dw,@TargetDate) <> @DesiredDayOfWeek

    set @TargetDate = @TargetDate + 1[/font]

    I would say the one that runs faster. You can always comment around your cleverness

  • First of all, this is a good article that explains how to solve a problem. There is one issue with the solution though. It works for the language setting of US English or any that has Sunday as the first day of the week. If you are in a different language setting the calculation does not work. Try this:

    /* 20111009 is a Sunday */

    SET LANGUAGE us_english ;

    SELECT

    @@LANGUAGE AS language_setting,

    DATEPART(weekday, '20111009') AS weekday_number,

    DATENAME(weekday, '20111009') AS weekday_name;

    DECLARE @StartDate DATE

    DECLARE @DaysOut TINYINT

    DECLARE @DesiredDayOfWeek TINYINT

    SET @StartDate = '20111009'

    --The starting date

    SET @DaysOut = 4

    --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6

    --The desired day of week

    SELECT

    DATEADD(d,

    (@DaysOut + (7 - (7 + DATEPART(weekday,

    DATEADD(d, @DaysOut, @StartDate)) -

    @DesiredDayOfWeek)) % 7), @StartDate)

    Go

    SET LANGUAGE italian

    SELECT

    @@LANGUAGE AS language_setting,

    DATEPART(weekday, '20111009') AS weekday_number,

    DATENAME(weekday, '20111009') AS weekday_name;

    DECLARE @StartDate DATE

    DECLARE @DaysOut TINYINT

    DECLARE @DesiredDayOfWeek TINYINT

    SET @StartDate = '20111009'

    --The starting date

    SET @DaysOut = 4

    --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6

    --The desired day of week

    SELECT

    DATEADD(d,

    (@DaysOut + (7 - (7 + DATEPART(weekday,

    DATEADD(d, @DaysOut, @StartDate)) -

    @DesiredDayOfWeek)) % 7), @StartDate)

    The first results in 2011-10-14 and the second results in 2011-10-15

  • No matter what, SQL makes my head itch. So no matter what, I comment...comment..comment.

    Thanks Itzik Ben-Gan for the in-line number generator, and tally table crusader Mr. Moden for getting N through my thick itchy skull.

    DECLARE @StartDate date

    DECLARE @DaysOut tinyint

    DECLARE @DesiredDayOfWeek tinyint

    DECLARE @TargetDate date

    DECLARE @MaxDays int

    SET @StartDate = '20111013' --The starting date

    SET @DaysOut = 1 --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6 --The desired day of week

    SET @TargetDate = dateadd(dd,@DaysOut,@StartDate)

    SET @MaxDays = @DaysOut + @DesiredDayOfWeek + 1 --Tally Table row count limiter

    ;WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

    SELECT TOP(1) @TargetDate = dateadd(dd,Tally_Table.N,@TargetDate)

    FROM (SELECT TOP(@MaxDays) N FROM Nums ORDER BY N) AS Tally_Table

    WHERE datepart(dw,dateadd(dd,Tally_Table.N,@TargetDate)) = @DesiredDayOfWeek

    SELECT @TargetDate

  • ron.mcdowell (10/13/2011)


    No matter what, SQL makes my head itch. So no matter what, I comment...comment..comment.

    Thanks Itzik Ben-Gan for the in-line number generator, and tally table crusader Mr. Moden for getting N through my thick itchy skull.

    DECLARE @StartDate date

    DECLARE @DaysOut tinyint

    DECLARE @DesiredDayOfWeek tinyint

    DECLARE @TargetDate date

    DECLARE @MaxDays int

    SET @StartDate = '20111013' --The starting date

    SET @DaysOut = 1 --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6 --The desired day of week

    SET @TargetDate = dateadd(dd,@DaysOut,@StartDate)

    SET @MaxDays = @DaysOut + @DesiredDayOfWeek + 1 --Tally Table row count limiter

    ;WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

    SELECT TOP(1) @TargetDate = dateadd(dd,Tally_Table.N,@TargetDate)

    FROM (SELECT TOP(@MaxDays) N FROM Nums ORDER BY N) AS Tally_Table

    WHERE datepart(dw,dateadd(dd,Tally_Table.N,@TargetDate)) = @DesiredDayOfWeek

    SELECT @TargetDate

    I like this solution, but you need to make sure you set @DesiredDayOfWeek to match the language settings in order to get the correct result, so you need to know that setting when making that choice. If you run this code with SET LANGUAGE us_english you get 2011-10-21, but if you change the language to Italian you get 2011-10-15. I think you want the first in all cases, but you need to make sure you know the language.

    I have an older blog post, http://wiseman-wiseguy.blogspot.com/2008/12/simple-but-effective-code-example.html that may give someone an idea on how to make either set of code to be work with any language setting.

  • Good article and good solution.

    Thanks to Jack et al for providing more options/solutions.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Here you go, Jack. Thanks for making me dig.

    --SET LANGUAGE italian

    --SET LANGUAGE us_english

    DECLARE @StartDate date

    DECLARE @DaysOut tinyint

    DECLARE @DesiredDayOfWeek tinyint

    DECLARE @TargetDate date

    DECLARE @MaxDays int

    SET @StartDate = '20111013' --The starting date

    SET @DaysOut = 1 --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6 --The desired day of week

    SET @TargetDate = dateadd(dd,@DaysOut,@StartDate)

    SET @MaxDays = @DaysOut + @DesiredDayOfWeek + 1 --Tally Table row count limiter

    -- For @@DATEFIRST explanation: See http://www.sqlservercentral.com/articles/DateFirst/69203/ (Thanks Divya Agrawal!)

    -- For Tally Table explanation: See http://www.sqlservercentral.com/articles/T-SQL/62867/ (Thanks Jeff Moden!)

    ;WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

    SELECT TOP(1) @TargetDate = dateadd(dd,Tally_Table.N,@TargetDate)

    FROM (SELECT TOP(@MaxDays) N FROM Nums ORDER BY N) AS Tally_Table

    WHERE (datepart(dw,dateadd(dd,Tally_Table.N,@TargetDate)) + @@DATEFIRST) % 7 = @DesiredDayOfWeek

    SELECT @TargetDate

  • ron.mcdowell (10/13/2011)


    Here you go, Jack. Thanks for making me dig.

    --SET LANGUAGE italian

    --SET LANGUAGE us_english

    DECLARE @StartDate date

    DECLARE @DaysOut tinyint

    DECLARE @DesiredDayOfWeek tinyint

    DECLARE @TargetDate date

    DECLARE @MaxDays int

    SET @StartDate = '20111013' --The starting date

    SET @DaysOut = 1 --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6 --The desired day of week

    SET @TargetDate = dateadd(dd,@DaysOut,@StartDate)

    SET @MaxDays = @DaysOut + @DesiredDayOfWeek + 1 --Tally Table row count limiter

    -- For @@DATEFIRST explanation: See http://www.sqlservercentral.com/articles/DateFirst/69203/ (Thanks Divya Agrawal!)

    -- For Tally Table explanation: See http://www.sqlservercentral.com/articles/T-SQL/62867/ (Thanks Jeff Moden!)

    ;WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

    SELECT TOP(1) @TargetDate = dateadd(dd,Tally_Table.N,@TargetDate)

    FROM (SELECT TOP(@MaxDays) N FROM Nums ORDER BY N) AS Tally_Table

    WHERE (datepart(dw,dateadd(dd,Tally_Table.N,@TargetDate)) + @@DATEFIRST) % 7 = @DesiredDayOfWeek

    SELECT @TargetDate

    I knew someone would come up with a language neutral solution. Good work, Ron!

  • So which is faster? On our clunky dev server, it takes about 30 seconds to iterate both methods one million times.

    Avg. execution time of each method:

    [font="Courier New"]

    One-liner : 0.014490000000000

    With Tally: 0.014822000000000

    Iterations: 1,000,000

    [/font]

    Now, if I had to do this a million times a day, I sure wouldn't generate an in-line tally table for each iteration (as was done during the above measurement).

    With actual numbers table:

    [font="Courier New"]

    One-liner : 0.015518000000000

    With Tally: 0.013313000000000

    Iterations: 1,000,000

    [/font]

    Your mileage will vary.

  • How about this:

    select @TargetDate=@StartDate+@DaysOut+

    case datepart(weekday,DATEADD( d, @DaysOut, @StartDate ))

    when 1 then case when @DesiredDayOfWeek>1 then @DesiredDayOfWeek-1 else @DesiredDayOfWeek+6 end

    when 2 then case when @DesiredDayOfWeek>2 then @DesiredDayOfWeek-2 else @DesiredDayOfWeek+5 end

    when 3 then case when @DesiredDayOfWeek>3 then @DesiredDayOfWeek-3 else @DesiredDayOfWeek+4 end

    when 4 then case when @DesiredDayOfWeek>4 then @DesiredDayOfWeek-4 else @DesiredDayOfWeek+3 end

    when 5 then case when @DesiredDayOfWeek>5 then @DesiredDayOfWeek-5 else @DesiredDayOfWeek+2 end

    when 6 then case when @DesiredDayOfWeek>6 then @DesiredDayOfWeek-6 else @DesiredDayOfWeek+1 end

    when 7 then @DesiredDayOfWeek

    end

  • To get the same results,

    select @TargetDate = dateadd(dd,@DaysOut,@StartDate)

    if datepart(dw,@TargetDate)=@DesiredDayOfWeek set @TargetDate = @TargetDate + 1

    while datepart(dw,@TargetDate) <> @DesiredDayOfWeek set @TargetDate = @TargetDate + 1

    select @TargetDate

    (This was in reply to someone else's suggestion... I added the second line)

  • Hi,

    Although I am a big fan of tally table and keen follower of Mr. Jeff Moden, I am not sure why we need a tally table here. It is just a simple calculation and the author has come up with a good algorithm. For brevity, I have used both the solutions (Original and Tally table) for a million rows table. The original solution was far superior than the tally table solution since there is no need to omit any looping kind of thing. (May be in some case we may need a tally table).

    By the way I have used both the scripts as Inline table valued functions (A tip also provided to me by Mr. Jeff Moden elsewhere)

    Following are the time statistics

    FOR ORIGINAL SOLUTION

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 31 ms, elapsed time = 1458 ms.

    FOR TALLY TABLE SOLUTION

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    SQL Server Execution Times:

    CPU time = 515 ms, elapsed time = 1899 ms.

    where I guess most of the elapsed time is taken while fetching 1 million rows.

    And when I ran both in a single batch and saw the actual execution plan the cost of tally table query was shown as 99% to 1% of original solution. So you can take your decision yourself. (You must test according to your needs and could come up with a situation where tally table could perform better e.g. some complex date range filter etc.)

    @Ron

    As far as your testing is concerned, I believe you have gone for same kind of static/increasing parameters in a loop (one row at a time), which would not have given the real performance counters.

    Following are the scripts I have ran to come to this conclusion

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE FUNCTION [dbo].[GET_WEEKDAY_PAST_Y_DAYS_AFTER_X_DAYS_ORIGINAL_SOLUTION]

    (

    @StartDate DATETIME

    ,@DaysOut TINYINT

    ,@DesiredDayOfWeek TINYINT

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    ( SELECT DATEADD(d,

    ( @DaysOut + ( 7 - ( ( 7 + DATEPART(weekday, DATEADD(d,@DaysOut, @StartDate))

    - @DesiredDayOfWeek ) % 7 ) ) ),

    @StartDate) AS DesiredDate

    )

    GO

    SET QUOTED_IDENTIFIER ON

    SET ANSI_NULLS ON

    GO

    CREATE FUNCTION [dbo].[GET_WEEKDAY_PAST_Y_DAYS_AFTER_X_DAYS_TALLY_TABLE_SOLUTION]

    (

    @StartDate DATETIME

    ,@DaysOut TINYINT

    ,@DesiredDayOfWeek TINYINT

    ,@TargetDate DATETIME

    ,@MaxDays INT

    )

    RETURNS TABLE

    WITH SCHEMABINDING

    AS

    RETURN

    WITH

    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),

    L1 AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),

    L2 AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),

    L3 AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),

    L4 AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),

    L5 AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),

    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)

    SELECT TOP(1) DATEADD(dd,Tally_Table.N,@TargetDate) DesiredDate

    FROM (SELECT TOP(@MaxDays) N FROM Nums ORDER BY N) AS Tally_Table

    WHERE DATEPART(dw,DATEADD(dd,Tally_Table.N,@TargetDate)) = @DesiredDayOfWeek

    GO

    SET NOCOUNT ON ;

    DECLARE @StartDate DATETIME

    DECLARE @DaysOut TINYINT

    DECLARE @DesiredDayOfWeek TINYINT

    --DECLARE @TargetDate DATETIME /* == DID NOT USE IT, SUPPLIED AT RUNTIME == */

    DECLARE @MaxDays INT

    SET @StartDate = '20111013' --The starting date

    SET @DaysOut = 1 --Number of days to count out (the Desired day is AFTER this count)

    SET @DesiredDayOfWeek = 6 --The desired day of week

    --SET @TargetDate = DATEADD(dd, @DaysOut, @StartDate) /* == DID NOT USE IT, SUPPLIED AT RUNTIME ==*/

    SET @MaxDays = @DaysOut + @DesiredDayOfWeek + 1 --Tally Table row count limiter

    SELECT [DT].[ShippedDate]

    , [DesiredDate]

    FROM [dbo].[DatesTable] AS DT

    CROSS APPLY [dbo].[GET_WEEKDAY_PAST_Y_DAYS_AFTER_X_DAYS_ORIGINAL_SOLUTION]([DT].[ShippedDate], @DaysOut, @DesiredDayOfWeek)

    SELECT [DT].[ShippedDate]

    , [DesiredDate]

    FROM [dbo].[DatesTable] AS DT

    CROSS APPLY [dbo].[GET_WEEKDAY_PAST_Y_DAYS_AFTER_X_DAYS_TALLY_TABLE_SOLUTION]( [DT].[ShippedDate], @DaysOut, @DesiredDayOfWeek, DATEADD(dd, @DaysOut, [DT].[ShippedDate]), @MaxDays )

    Cheers.

    Best Regards,

    Usman Butt

Viewing 14 posts - 1 through 13 (of 13 total)

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