Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Use SQL To Get Thanksgiving days 6

Status
Not open for further replies.
SQLDenis

SQLDenis

Programmer
Oct 1, 2005
5,575
0
0
US
I will give stars to the person(s) who can come up with a better/faster/easier solution to get the next(n) thanksgiving days by using SQL

Here is my code

CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <(10 *365)
BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END


SET DATEFIRST 7

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

SELECT MAX((DATEADD(d,number,@dtmStardate))) AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(dw,DATEADD(d,number,@dtmStardate)) = 5
AND DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
ORDER BY 1

--By the way Happy Thanksgiving

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Sort by date Sort by votes
For some of us outside United States...

Thanksgiving day occurs every 4th Thursday in November, right?

If true, then result for Y2006 is wrong [pipe].

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Okay new and Improved
CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <(10 *365)
BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END


SET DATEFIRST 7

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

SELECT case when count(DATEPART(dw,DATEADD(d,number,@dtmStardate))) <> 5 then MAX((DATEADD(d,number,@dtmStardate)))
else MAX((DATEADD(d,number,@dtmStardate))) -7 end
AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(dw,DATEADD(d,number,@dtmStardate)) = 5
AND DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
ORDER BY 2


Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
> Isn't it last Thursday in November?

In that case, result for Y2015 is wrong [pipe]

Btw. can we use permanent calendar table? I guess not...




------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
Yup, since I was born... several months more or less :)

Btw. will this do the trick?
Code: 
-- input arguments
declare @fromyear smallint, @toyear smallint
set @fromyear = 2005; set @toyear = 2015

-- actual code
set datefirst 5
declare @blah table ( yr smallint, tnxday smalldatetime )
declare @yr smallint; set @yr = @fromyear
declare @date smalldatetime

while @yr <= @toyear
begin
	set @date = 7 + dateadd(mm, 10, dateadd(yy, @yr-1900, 0))
	set @date = dateadd(wk, 3, @date - datepart(dw, @date))
	insert into @blah values (@yr, @date)

	set @yr = @yr + 1
end

select * from @blah

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
That will do the trick
My code is faster (query cost 23%) if you take into account that I have a permanent auxiliary table that I join against. If I use the temp table like I showed in my example then your code is faster
Star for you
I was born in Croatia also (I grew up in Amsterdam) but live in Princeton NJ at this moment

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote


How about this: it takes out one filter, also the table is 7 times smaller.

Code: 
drop table #NumberPivot
CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <(10 *365)
BEGIN
    INSERT INTO #NumberPivot
    VALUES (@intLoopCounter)
    SELECT @intLoopCounter = @intLoopCounter + 7
END


SET DATEFIRST 7

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

SELECT MAX((DATEADD(d,number,@dtmStardate))) AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
ORDER BY 1
 
Upvote 0 Downvote
Here is some code that I use in one of my apps.

Code: 
ALTER procedure [dbo].[asp_LoadHolidays]
	@YearsAhead smallint
as

set nocount on

declare @Date datetime
...
/*Thanksgiving (Fourth Thursday in November*/
set @Date = '11/1/' + convert(varchar(4), datepart(yy, dateadd(yy, @YearsAhead, getdate())))

while datepart(dw, @Date) <> 5 /*Find the First Thursday*/
BEGIN
	set @Date = dateadd(dd, +1, @Date)
END
/*Now move ahead 3 weeks to the 4th thursday*/
set @Date = dateadd(ww, 3, @Date)
select @Date
It's fairly straight foward code. Get the first day of November. Now find the first Thursday, then jump forward to the 4th thursday. Return the date.

I actually use this for a bunch of holidays that get loaded into a holiday table to that the ticketing system doesn't send emails that we need to work on tickets on days that the office is closed (it waits until midnight the next day, then bugs us all).

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)
[noevil]
 
Upvote 0 Downvote
This one is ugly:
Code: 
declare @maxyr smallint; set @maxyr = 2015

declare @blah table( yr smallint )
while @maxyr >= year(getdate())
begin
	insert into @blah values (@maxyr)
	set @maxyr = @maxyr -1
end

select yr, dateadd(wk, datediff(wk, 0, convert(datetime, convert(varchar(4), yr) + '1110') ), 0) + 17 as tnxDay
from @blah
order by yr

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)
[banghead]
 
Upvote 0 Downvote
maswien, your result is incorrect
2006-11-30 00:00:00.000 should be 2006-11-23 00:00:00.000
this fixes it

drop table #NumberPivot
CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <(10 *365)
BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)
SELECT @intLoopCounter = @intLoopCounter + 7
END


SET DATEFIRST 7

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

SELECT CASE WHEN COUNT(DATEPART(dw,DATEADD(d,number,@dtmStardate))) <> 5
THEN MAX((DATEADD(d,number,@dtmStardate)))
ELSE MAX((DATEADD(d,number,@dtmStardate))) -7 END AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(dw,DATEADD(d,number,@dtmStardate)) = 5
AND DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
ORDER BY 2

I am giving you a star


Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote

Ok, you are right, your version can still be simplified as:

Code: 
drop table #NumberPivot
CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <(10 *365)
BEGIN
    INSERT INTO #NumberPivot
    VALUES (@intLoopCounter)
    SELECT @intLoopCounter = @intLoopCounter + 7
END

SET DATEFIRST 7

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

SELECT case 
       when count(*) < 5 
       then MAX((DATEADD(d,number,@dtmStardate))) 
       else MAX((DATEADD(d,number,@dtmStardate))) - 7 
       end AS ThanksGivingDay,
       DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
ORDER BY 1
 
Upvote 0 Downvote
This is my final version, will only work if the first day is a Thursday of course

CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

DECLARE @dtmEnddate DATETIME
SELECT @dtmEnddate ='2015-11-30 00:00:00.000'

WHILE @intLoopCounter <(select datediff(d,@dtmStardate,@dtmEnddate))

BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +7
END


SET DATEFIRST 7

SELECT CASE WHEN COUNT(*) <> 5
THEN MAX((DATEADD(d,number,@dtmStardate)))
ELSE MAX((DATEADD(d,number,@dtmStardate))) -7 END AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(dw,DATEADD(d,number,@dtmStardate)) = 5
AND DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))
ORDER BY 2

drop table #NumberPivot

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote

st dennis, do you really read my code?

"DATEPART(dw,DATEADD(d,number,@dtmStardate)) = 5" is not required because it can't filter out any row in the temporary table given the first day is Thursday and the table is inserted at step 7. Also the order by DATEPART(mm,DATEADD(d,number,@dtmStardate)) is not required because of the filter DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11.


 
Upvote 0 Downvote
True, the problem is that I already have a table with numbers and just included the temp table so that people could run the code in 1 shot
Anyway I will give you another star

And this is the final code

CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

DECLARE @dtmEnddate DATETIME
SELECT @dtmEnddate ='2015-11-30 00:00:00.000'

WHILE @intLoopCounter <(select datediff(d,@dtmStardate,@dtmEnddate))

BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)
--+ 7 will only work if you start from a Thursday, use +1 for other days
SELECT @intLoopCounter = @intLoopCounter +7
END


SET DATEFIRST 7

SELECT CASE WHEN COUNT(*) <> 5
THEN MAX((DATEADD(d,number,@dtmStardate)))
ELSE MAX((DATEADD(d,number,@dtmStardate))) -7 END AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))


drop table #NumberPivot

Denis The SQL Menace
SQL blog:
Personal Blog:
 
Upvote 0 Downvote
Thanks SQLdenis, sorry for the typo, the order by is good, I meant
"DATEPART(mm,DATEADD(d,number,@dtmStardate))" shouldn't be included in the group by.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
202
Volvere
V
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
166
pjw001
pjw001
Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
113
Cpreston
Cpreston
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
119
Mike Lewis
Mike Lewis
Cpreston
  • Question
Seconds to minutes 2
Replies
5
Views
274
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top