|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Today @ 7:31 AM
Points: 1,163,
Visits: 2,601
|
|
An this is slightly faster again, by avoiding the string comparison ( = 'Friday'):
SELECT YEAR(DATEADD(mm,N-1,'17530113')), COUNT(*) FROM dbo.Tally WHERE N < DATEDIFF(mm,'17530113','99990101') AND (DATEPART(dw,DATEADD(mm,N-1,'17530113')) + @@DATEFIRST) % 7 = 6 GROUP BY YEAR(DATEADD(mm,N-1,'17530113')) HAVING count(*) = 3 ORDER BY YEAR(DATEADD(mm,N-1,'17530113'))
-- Nigel Useful Links: How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Thursday, April 11, 2013 1:43 PM
Points: 34,
Visits: 115
|
|
Nigel, Yes, your tally approach is much faster. 100 ms vs 1400 ms on my machine. Thanks, Bill
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Thursday, December 13, 2012 4:18 AM
Points: 2,798,
Visits: 196
|
|
If the current date is equal to 13 then your query will give that as Friday.
IF (DATEPART(dd, @currDate) <> 13)
what about adding one more condition with above one OR (DATEPART(dw, @currDate) <> 1)
then I think your query is perfect.
|
|
|
|