Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get next 1st thursday Expand / Collapse
Author
Message
Posted Tuesday, December 20, 2011 2:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 2:48 PM
Points: 9, Visits: 435
Comments posted to this topic are about the item Get next 1st thursday
Post #1224758
Posted Wednesday, December 28, 2011 8:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:11 AM
Points: 1, Visits: 90
SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)),3)
Post #1227622
Posted Friday, January 20, 2012 2:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:04 AM
Points: 1,178, Visits: 2,648
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

Post #1239246
Posted Friday, January 20, 2012 2:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:04 AM
Points: 1,178, Visits: 2,648
Matt,

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

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


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

Post #1239251
Posted Friday, January 20, 2012 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:33 PM
Points: 106, Visits: 365
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.
Post #1239607
Posted Tuesday, January 24, 2012 8:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:04 AM
Points: 1,178, Visits: 2,648
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

Post #1240952
Posted Tuesday, January 24, 2012 1:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:33 PM
Points: 106, Visits: 365
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.
Post #1241164
Posted Wednesday, January 25, 2012 1:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:04 AM
Points: 1,178, Visits: 2,648
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

Post #1241421
Posted Wednesday, January 25, 2012 6:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:33 PM
Points: 106, Visits: 365
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.
Post #1241500
Posted Wednesday, January 25, 2012 7:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 8, 2014 8:04 AM
Points: 1,178, Visits: 2,648
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!


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

Post #1241522
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse