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 Tuesday, August 18, 2009 5:04 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 22, 2012 7:08 AM
Points: 233, Visits: 112
Comments posted to this topic are about the item When will the next 13th fall on a Friday?

Thanks,

Ashka Modi
Software Engineer || credEcard Technologies (india) Pvt. Ltd.
Post #773130
Posted Thursday, August 27, 2009 12:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 8:46 AM
Points: 5, Visits: 335
How about set-based approach:
SET DATEFIRST 1
;WITH days(Date) AS
(
--Get 13th of this month
SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-13' AS DATETIME)
UNION ALL
--Get 13th of next months
SELECT DATEADD(MONTH, 1, Date) FROM days
)
SELECT TOP 1 * FROM days WHERE DATEPART(dw, Date) = 5 AND Date > GETDATE()

Post #778068
Posted Thursday, August 27, 2009 2:52 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 22, 2012 7:08 AM
Points: 233, Visits: 112
Good one! :) but I am not much comfortable with UNION ALL. what you say?

Thanks,

Ashka Modi
Software Engineer || credEcard Technologies (india) Pvt. Ltd.
Post #778108
Posted Thursday, August 27, 2009 3:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 8:46 AM
Points: 5, Visits: 335
"UNION ALL" is a requirement in CTE
Post #778134
Posted Thursday, August 27, 2009 4:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, October 22, 2012 7:08 AM
Points: 233, Visits: 112
SET DATEFIRST 1
;WITH days(Date) AS
(
--Get 13th of this month
SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-13' AS DATETIME)
UNION ALL
--Get 13th of next months
SELECT DATEADD(MONTH, 1, Date) FROM days
)
SELECT * FROM days
SELECT TOP 1 * FROM days WHERE DATEPART(dw, Date) = 5 AND Date > GETDATE()

Have you check this? check Execution plan also.


Thanks,

Ashka Modi
Software Engineer || credEcard Technologies (india) Pvt. Ltd.
Post #778140
Posted Thursday, August 27, 2009 4:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 8:46 AM
Points: 5, Visits: 335
I'm not sure what you mean.

"SELECT * FROM days" will fail because of recursion limit. But original idea was to get only the first Friday, the 13th. As there is always at least one such day in a year, there will be no more than 23 iterations.


Is there something wrong with execution plan?

Sorry for asking, but are you familiar with common table expressions (CTE)?


Post #778162
Posted Thursday, August 27, 2009 5:18 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
Another set based solution, this time using the good old Tally table. ((c) Jeff Moden et al. 19xx-2009)

 SELECT TOP 1
DATEADD(dd,N-1,GETDATE())
FROM
Tally
WHERE
n < 366 --== One years worth of days
AND
DAY(DATEADD(dd,N-1,GETDATE())) = 13
AND
--==
--== Using @@DATEFIRST and the modulus operator we don't need to change or
--== assume anything about the current DATEFIRST setting
--==
(@@DATEFIRST + DATEPART(dw,DATEADD(dd,N-1,GETDATE()))) %7 = 6
ORDER BY
N

The execution plan for this indicates 100% of the query is spent doing a clustered index seek (nice!). That's if you build your tally table with a clustered index of course, as demonstrated in Jeff's tally table article.

And, as I indicated in the comments, you don't need to mess about with the DATEFIRST setting.


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

Post #778175
Posted Thursday, August 27, 2009 8:29 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:11 AM
Points: 36,995, Visits: 31,516
Rimvydas Gurskis (8/27/2009)
How about set-based approach:
SET DATEFIRST 1
;WITH days(Date) AS
(
--Get 13th of this month
SELECT CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '-13' AS DATETIME)
UNION ALL
--Get 13th of next months
SELECT DATEADD(MONTH, 1, Date) FROM days
)
SELECT TOP 1 * FROM days WHERE DATEPART(dw, Date) = 5 AND Date > GETDATE()



Correct me if I'm wrong, but that appears to be a recursive CTE .... which ISN'T set based at all. In fact, recurrsive CTE's are sometimes slower than a memory only While Loop.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #778344
Posted Thursday, August 27, 2009 11:04 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
I agree with Rimvydas' approach.
And for performance concerns on CTEs on this particular problem, this gives all the years from 1753 to 9999 that have 3 Friday the thirteenths.
It runs in about 2 to 3 seconds on my small test machine.
with thirteen (thedate) as(
select cast('01/13/1753' as datetime) as thedate
union all
select DATEADD(mm, 1, thedate)
from thirteen
where thedate < '01/01/9999'
)
select year(thedate) yr,
count(*) cnt
from thirteen
where DATENAME(dw, thedate) = 'Friday'
group by year(thedate)
having count(*) = 3
OPTION (MAXRECURSION 0)

Post #778494
Posted Friday, August 28, 2009 2:16 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 2:18 AM
Points: 1,180, Visits: 2,650
Bill Coale (8/27/2009)
I agree with Rimvydas' approach.
And for performance concerns on CTEs on this particular problem, this gives all the years from 1753 to 9999 that have 3 Friday the thirteenths.
It runs in about 2 to 3 seconds on my small test machine.


If your concerned with performance I'd go for the tally table approach, The following executes in around 100ms on my machine.

    SELECT 
YEAR(DATEADD(mm,N-1,'17530113')),
COUNT(*)
FROM
dbo.Tally
WHERE
N < DATEDIFF(mm,'17530113','99990101')
AND
DATENAME(dw, DATEADD(mm,N-1,'17530113')) = 'Friday'
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 #778936
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse