October 12, 2011 at 10:49 pm
Comments posted to this topic are about the item Need the First Tuesday Beyond Y days after X Date
October 13, 2011 at 5:11 am
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 )
October 13, 2011 at 5:59 am
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]
October 13, 2011 at 6:18 am
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
October 13, 2011 at 6:49 am
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
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2011 at 8:18 am
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
October 13, 2011 at 8:53 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2011 at 9:11 am
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
October 13, 2011 at 1:19 pm
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
October 13, 2011 at 1:26 pm
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!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2011 at 2:40 pm
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.
October 13, 2011 at 4:41 pm
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
October 13, 2011 at 4:49 pm
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)
October 14, 2011 at 4:51 am
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.)
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