Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Get next 1st thursday Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, December 20, 2011 2:13 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, December 04, 2013 7:33 AM Points: 9, Visits: 432
 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 Group: General Forum Members Last Login: 2 days ago @ 2:10 AM Points: 1, Visits: 60
Post #1227622
 Posted Friday, January 20, 2012 2:24 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:16 AM Points: 1,169, Visits: 2,615
 Nice one matt, I thought the original looked well overcomplicated when I saw it.thanks
Post #1239246
 Posted Friday, January 20, 2012 2:42 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:16 AM Points: 1,169, Visits: 2,615
 Matt,There is however a difference I'd not spotted initially between the solutions for the 1 Feb 2012The original solutions result is: 2 Feb 2012Yours is: 1 Mar 2012
Post #1239251
 Posted Friday, January 20, 2012 10:15 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, February 11, 2013 5:58 PM Points: 100, Visits: 347
 Here is an alternate version using a Tally table.`DECLARE @SelectedDate DATE = '2012-02-01' --GETDATE()DECLARE @DesiredDay TINYINT = 5SELECT TOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDayFROM ( SELECT DATEADD(DAY, N, @SelectedDate) AS NextDay FROM Tally WHERE N <= 14 ) xWHERE DATEPART(WEEKDAY, NextDay) = @DesiredDayORDER BY NextDay`It should be easy to turn into a function.
Post #1239607
 Posted Tuesday, January 24, 2012 8:59 AM
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:16 AM Points: 1,169, Visits: 2,615
 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 theDayFROM( 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 ) ThursdaysWHERE theRank = 1 -- The first thursday AND theDay > @now -- After the target dateORDER BY theDay`
Post #1240952
 Posted Tuesday, January 24, 2012 1:19 PM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, February 11, 2013 5:58 PM Points: 100, Visits: 347
 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 Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:16 AM Points: 1,169, Visits: 2,615
 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
Post #1241421
 Posted Wednesday, January 25, 2012 6:22 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Monday, February 11, 2013 5:58 PM Points: 100, Visits: 347
 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 = 5SELECT TOP 1 NextDay, DATENAME(WEEKDAY, NextDay) AS NameOfDayFROM ( SELECT DATEADD(DAY, N, @SelectedDate) AS NextDay FROM Tally WHERE N <= 40 ) xWHERE DATEPART(WEEKDAY, NextDay) = @DesiredDay AND DAY(NextDay) <= 7ORDER 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 Group: General Forum Members Last Login: Wednesday, November 06, 2013 2:16 AM Points: 1,169, Visits: 2,615
 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!
Post #1241522

 Permissions