Log in  ::  Register  ::  Not logged in

## When will the next 13th fall on a Friday?

 Author Message Ashka Modi SSC Veteran Group: General Forum Members Points: 261 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. Rimvydas Gurskis Grasshopper Group: General Forum Members Points: 22 Visits: 432 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()` Ashka Modi SSC Veteran Group: General Forum Members Points: 261 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. Rimvydas Gurskis Grasshopper Group: General Forum Members Points: 22 Visits: 432 "UNION ALL" is a requirement in CTE Ashka Modi SSC Veteran Group: General Forum Members Points: 261 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 daysSELECT 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. Rimvydas Gurskis Grasshopper Group: General Forum Members Points: 22 Visits: 432 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)? nigel. SSCrazy Group: General Forum Members Points: 2116 Visits: 2877 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 = 6ORDER 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. --NigelUseful Links:How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden Jeff Moden SSC Guru Group: General Forum Members Points: 114673 Visits: 41394 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 ModenRBAR 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.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Bill Coale-478581 Valued Member Group: General Forum Members Points: 60 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 allselect 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(*) = 3OPTION (MAXRECURSION 0)` nigel. SSCrazy Group: General Forum Members Points: 2116 Visits: 2877 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'))` --NigelUseful Links:How to post data/code on a forum to get the best help The "Numbers" or "Tally" Table - Jeff Moden