SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get next 1st thursday


Get next 1st thursday

Author
Message
harsha.majety
harsha.majety
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 449
Comments posted to this topic are about the item Get next 1st thursday
matt_chen
matt_chen
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 110
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)),3)
nigel.
nigel.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5096 Visits: 2949
Nice one matt, I thought the original looked well overcomplicated when I saw it.

thanks

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

nigel.
nigel.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5096 Visits: 2949
Matt,

There is however a difference I'd not spotted initially Blush between the solutions for the 1 Feb 2012

The original solutions result is: 2 Feb 2012
Yours is: 1 Mar 2012 w00t

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

fahey.jonathan
fahey.jonathan
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 402
Here is an alternate version using a Tally table.

DECLARE   @SelectedDate   DATE = '2012-02-01' --GETDATE()
DECLARE @DesiredDay TINYINT = 5

SELECT TOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDay
FROM (
SELECT DATEADD(DAY, N, @SelectedDate) AS NextDay
FROM Tally
WHERE N <= 14
) x
WHERE DATEPART(WEEKDAY, NextDay) = @DesiredDay
ORDER BY NextDay



It should be easy to turn into a function.
nigel.
nigel.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5096 Visits: 2949
fahey.jonathan (1/20/2012)
Here is an alternate version using a Tally table.


Jonathan,
Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.


Here's another version loosely based on your Tally table idea.

I'll try to explain what it does:
1. Gets all days for this and the next month.
2. Reduces that set to just Thursdays
3. Ranks partitioned by month, to number the Thursdays 1 - n
4. Gets the top Thursday with a rank of 1 that is greater than the target date


DECLARE @now DATETIME = '20120201'

SELECT TOP 1
theDay
FROM
(
SELECT
theDay,
--==
--== Rank the thursdays 1-4 (or possibly 5)
--==
theRank = RANK() OVER(PARTITION BY MONTH(theDay) ORDER BY theDay)
FROM
(
--==
--== Get all the days in this and next month
--==
SELECT
DATEADD(dd,n-1,DATEADD(mm, DATEDIFF(mm,'19000101',@now ) ,'19000101')) theDay
FROM
Tally
WHERE
N <=
DATEDIFF(dd,
DATEADD(mm, DATEDIFF(mm,'19000101',@now) ,'19000101'),
DATEADD(mm, 2+DATEDIFF(mm,'19000101',@now) ,'19000101'))
) alldays
WHERE
--==
--== Get just the Thursdays (note the adjustment for a DATEFIRST setting other than 7)
--==
(@@DATEFIRST + DATEPART(dw,theDay)) % 7 = 5
) Thursdays
WHERE
theRank = 1 -- The first thursday
AND
theDay > @now -- After the target date
ORDER BY theDay



--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

fahey.jonathan
fahey.jonathan
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 402
Jonathan,
Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.


I misread the original post. I thought the goal was to find the next Thursday, not the first Thursday of a month past today. Sorry.
nigel.
nigel.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5096 Visits: 2949
fahey.jonathan (1/24/2012)
Jonathan,
Nearly but not quite, that just gets next thursday. Not the next first thursday of the month.


I misread the original post. I thought the goal was to find the next Thursday, not the first Thursday of a month past today. Sorry.


Easily done :-), to be honest it wasn't particularly well defined in the article

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

fahey.jonathan
fahey.jonathan
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1220 Visits: 402
Because we know that the first Thursday of a month must be within 40 days of the selected date and must have a day number between 1 and 7, I can modify my original query with those parameters:

DECLARE   @SelectedDate   DATE = '2012-02-02' --GETDATE()
DECLARE @DesiredDay TINYINT = 5
SELECT TOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDay
FROM (
SELECT DATEADD(DAY, N, @SelectedDate) AS NextDay
FROM Tally
WHERE N <= 40
) x
WHERE DATEPART(WEEKDAY, NextDay) = @DesiredDay
AND DAY(NextDay) <= 7
ORDER BY NextDay



I think it is that easy. Let me know if I missed something.
nigel.
nigel.
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5096 Visits: 2949
Jonathan,

I think you may have cracked it there, I suspected mine was too complicated.

With one slight modification to your where clause to account for different DATEFIRST settings:

WHERE
(@@DATEFIRST + DATEPART(WEEKDAY, NextDay)) %7 = @DesiredDay
AND
DAY(NextDay) <= 7



Nice one! Cool

--
Nigel
Useful Links:
How to post data/code on a forum to get the best help
The "Numbers" or "Tally" Table - Jeff Moden

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search