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

When will the next 13th fall on a Friday? Expand / Collapse
Author
Message
Posted Friday, August 28, 2009 2:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:47 AM
Points: 1,174, Visits: 2,635
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

Post #778938
Posted Friday, August 28, 2009 7:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 13, 2013 2:07 PM
Points: 34, Visits: 117
Nigel,
Yes, your tally approach is much faster. 100 ms vs 1400 ms on my machine.
Thanks,
Bill
Post #779057
Posted Thursday, September 10, 2009 12:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, April 12, 2014 11:48 AM
Points: 2,819, Visits: 202
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.

Post #785517
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse