Get next 1st thursday

 Author Message harsha.majety Ten Centuries Group: General Forum Members Points: 1169 Visits: 449 Comments posted to this topic are about the item Get next 1st thursday matt_chen SSC Rookie Group: General Forum Members Points: 27 Visits: 110 SELECT DATEADD(WEEK,DATEDIFF(WEEK,0,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,0)),3) nigel. SSCrazy Eights Group: General Forum Members Points: 9276 Visits: 3064 Nice one matt, I thought the original looked well overcomplicated when I saw it.thanks --NigelUseful Links:How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden nigel. SSCrazy Eights Group: General Forum Members Points: 9276 Visits: 3064 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 --NigelUseful Links:How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden fahey.jonathan SSCrazy Group: General Forum Members Points: 2513 Visits: 409 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. nigel. SSCrazy Eights Group: General Forum Members Points: 9276 Visits: 3064 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` --NigelUseful Links:How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden fahey.jonathan SSCrazy Group: General Forum Members Points: 2513 Visits: 409 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. SSCrazy Eights Group: General Forum Members Points: 9276 Visits: 3064 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 --NigelUseful Links:How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden fahey.jonathan SSCrazy Group: General Forum Members Points: 2513 Visits: 409 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. nigel. SSCrazy Eights Group: General Forum Members Points: 9276 Visits: 3064 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! --NigelUseful Links:How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden