## Tuesday, December 8, 2009

### Friday the 13th 2009: Cluster of Terror

This blog entry is participating in the very first T-SQL Tuesday, hosted this month by Adam Machanic. You are invited to visit his blog to join this rollicking blog party and read more blogs participating in this month’s theme: Date/Time Tricks.

Approximately 715,298 articles have been written over the past 20 years or so regarding the handling of dates and times in T-SQL. So, instead of fruitlessly trying to come up with an earth-shattering new clever way of handling dates that no one’s ever seen before, I just thought I’d have some fun with some analysis of a very specific date that we all know and fear love.

By the way, before we proceed, as long as we’re talking about date-related stuff, I should remind you that the deadline for TSQL Challenge #18 (Generate Text-Formatted Month Calendars) is on December 14th, so check it out and give it a try.

Okay, on with our fun stuff…

Is it just my imagination, or have we experienced a bunch of Friday the 13th’s lately?

For those of you who are not up-to-speed on superstitions, the number 13 is an unlucky number in many cultures, and, similarly, Friday is an unlucky day of the week, and so Friday the 13th is double bad luck. People who have a genuine fear of Friday the 13th suffer from paraskevidekatriaphobia, which is a concoction of Greek words for Friday (Paraskeví), thirteen (dekatreís), and fear (phobía).

Let’s do a little investigating into Friday the 13th. First, we’ll build a Calendar table of every single date in the 1247-year range of January 1, 1753 (the minimum date of the T-SQL datetime datatype) to December 31, 2999 (just a reasonable stopping point). That comes out to 455,457 dates, but our friend T-SQL can create that table in less than a second:

`if object_id('tempdb..#Calendar') is not null drop table #Calendargowith   L0(N) as (select 0 union all select 0 union all select 0) /* 3 Rows */ ,L1(N) as (select 0 from L0 A cross join L0 B)             /* 9 Rows (3x3) */ ,L2(N) as (select 0 from L1 A cross join L1 B)             /* 81 Rows (9x9) */ ,L3(N) as (select 0 from L2 A cross join L2 B)             /* 6561 Rows (81x81) */ ,L4(N) as (select 0 from L3 A cross join L3 B)             /* 43,046,721 Rows */ ,Numbers(Num) as (select row_number() over (order by (select 0)) from L4)select [Date]      ,[Year]=year([Date])      ,[Month]=month([Date])      ,[Day]=day([Date])      ,DayOfWeek=datename(weekday,[Date])into #Calendarfrom Numbers cross apply (select [Date]=convert(datetime,dateadd(day,Num-1,'17530101'))) F1where Num between 1 and datediff(day,'17530101','29991231')+1 `
Now we can check out the 13th and see how often it falls on the 7 days of the week:

`select DayOfWeek      ,NumOccurrences=count(*)from #Calendarwhere [Day]=13group by DayOfWeekorder by NumOccurrences desc/*DayOfWeek NumOccurrences--------- --------------Friday              2146Wednesday           2142Sunday              2142Tuesday             2137Monday              2133Saturday            2132Thursday            2132*/`
Hmmm… interesting. The 13th occurs on Friday more than any other day of the week. That’s kind of scary, don’t you think?

In fact, as you can see below, Friday the 13th is in the family of most frequently occurring dates in history (and the future):

`with HighestOccurrences as(  select   top 1   with ties DayOfWeek           ,[Day]  from #Calendar  group by DayOfWeek,[Day]  order by count(*) desc)select [MostFrequentlyOccurringDay]          =DayOfWeek          +' the '          +ltrim(str([Day]))          +case             when [Day] in (1,21,31) then 'st'             when [Day] in (2,22) then 'nd'             when [Day] in (3,23) then 'rd'             else 'th'           endfrom HighestOccurrencesorder by [Day],DayOfWeek/*MostFrequentlyOccurringDay--------------------------Sunday the 1stMonday the 2ndTuesday the 3rd...Wednesday the 11thThursday the 12thFriday the 13thSaturday the 14thSunday the 15th...Thursday the 26thFriday the 27thSaturday the 28th*/`
Of course, logically, we can see that this also means that Thursday the 12th and Saturday the 14th and so on down to Sunday the 1st and up to Saturday the 28th occur the most frequently, but still… it’s kind of disconcerting that the most-feared day of all occurs the most often.

Let’s build a table of just the Friday the 13th dates:

`if object_id('tempdb..#Friday13s') is not null drop table #Friday13sgoselect [Date]      ,[Year]      ,[Month]into #Friday13sfrom #Calendarwhere DayOfWeek='Friday' and [Day]=13 `
That table is now populated with all the Friday-the-13th dates from 1753 to 2999… There are 2146 of them.

Wait a minute…

If you add the digits of 2146, you get 2 + 1 + 4 + 6 = 13.

Now that’s an interesting (and somewhat unsettling) coincidence, isn’t it? Ha-ha-ha.

Ahem… Oh well, never mind… Let’s move on…

Using that table, we can see that a Friday the 13th occurs every single year without exception:

`select Result=case                when (select count(distinct [Year]) from #Friday13s)=2999-1753+1                then 'Yep... it occurs every single year'                else 'Nope... there are some years when it doesn''t happen'              end/*Result----------------------------------Yep... it occurs every single year*/`
Apparently there’s no escape!

And here’s an interesting tidbit: Every century (except the 1700s because they are only partially represented in our table) contains the exact same number of Friday the 13th’s:

`select Century      ,NumOccurrences=count(*)from #Friday13scross apply (select Century=str([Year]/100*100,4)+'s') F1where [Year]>=1800group by Centuryorder by Century/*Century NumOccurrences------- --------------1800s              1721900s              1722000s              1722100s              1722200s              1722300s              1722400s              1722500s              1722600s              1722700s              1722800s              1722900s              172*/`
Fascinating…

Now I mentioned earlier that I thought that we had experienced a bunch of Friday the 13th’s lately. Was I right? How many did we have this year (2009)?:

`select [Date]from #Friday13swhere [Year]=2009order by [Date]/*Date-----------------------2009-02-13 00:00:00.0002009-03-13 00:00:00.0002009-11-13 00:00:00.000*/`
Three of them! A Triple! We just had one last month in November, and we had two months in a row with February and March! Omigosh!

Now that makes me wonder… What years contain the most occurrences of Friday the 13th?:

`with HighestOccurrences as(  select   top 1   with ties [Year]           ,NumOccurrences=count(*)  from #Friday13s  group by [Year]  order by NumOccurrences desc)select [Year]      ,NumOccurrencesfrom HighestOccurrencesorder by [Year]/*Year NumOccurrences---- --------------1761              31764              31767              3...1931              31942              31953              31956              31959              31970              31981              31984              31987              31998              32009              32012              32015              32026              32037              32040              32043              3...2989              32992              32995              3*/`
Yikes! Sure enough… Three times is the most it can occur in a year, and this year (2009) is one of those! And we haven’t experienced a Triple like that since 1998!

Wait a minute… Look at that list again. It looks like we’re going to experience another Triple in just 3 short years… in 2012! And wait! We’ll have another Triple just 3 years after that… in 2015!

Whoa! I thought we had experienced many Friday the 13th’s lately, but it looks like we are only at the beginning of a really scary cluster of them! It’s a Triple-Triple-Triple (3-3-3) Cluster! Three Friday the 13th’s each occurring in three different years that are three years apart from each other.

This calls for more investigation. Let’s look at all the years that have a Triple… and then cluster them chronologically in groups of three… and then find the Clusters that span the least amount of time (i.e. the tightest, densest, scariest Clusters):

`with TripleFri13Years as(  select [Year]        ,RowNum=row_number() over (order by [Year])  from #Friday13s  group by [Year]  having count(*)=3),TripleFri13YearClusters as(  select FirstTriple=A.[Year]        ,SecondTriple=B.[Year]        ,ThirdTriple=C.[Year]        ,YearSpan        ,YearSpanRank=rank() over (order by YearSpan)  from TripleFri13Years A  join TripleFri13Years B on A.RowNum+1=B.RowNum  join TripleFri13Years C on B.RowNum+1=C.RowNum  cross apply (select YearSpan=C.[Year]-A.[Year]+1) F1)select FirstTriple      ,SecondTriple      ,ThirdTriple      ,YearSpanfrom TripleFri13YearClusters where YearSpanRank=1  /* The Shortest Span */order by FirstTriple/*FirstTriple SecondTriple ThirdTriple YearSpan----------- ------------ ----------- --------       1761         1764        1767        7       1789         1792        1795        7       1801         1804        1807        7       1829         1832        1835        7       1857         1860        1863        7       1885         1888        1891        7       1925         1928        1931        7       1953         1956        1959        7       1981         1984        1987        7       2009         2012        2015        7       2037         2040        2043        7       2065         2068        2071        7       2093         2096        2099        7       ...       2933         2936        2939        7       2961         2964        2967        7       2989         2992        2995        7*/`
Oh no!

It looks like the 3-3-3 Cluster is the shortest cluster time span possible, and therefore, we are now on the verge of the worst possible Triple Cluster of Triple Friday-the-13th Years! The dreaded 3-3-3 cluster of 2009-2012-2015! The last time this kind of dense 3-3-3 cluster happened was the 7 year span of 1981-1984-1987.

Wait… Let me think… Did anything terrible happen from 1981 to 1987? Well, AIDS was first reported in 1981. And… uh-oh… (GULP)… The H1N1 Swine Flu Pandemic started this very year in 2009!

OH… MY… GAWD! It can’t be a coincidence!

So, let me make sure I got this straight… it seems that 2009 has the highest possible occurrences of Friday the 13th’s and it’s the beginning of a dreaded 3-3-3 Cluster. This doesn’t look good.

Wait!… I’ve gotta check this out… We already saw that every century has the same amount of Friday the 13th’s, but what about 3-3-3 Clusters?:

`with TripleFri13Years as(  select [Year]        ,RowNum=row_number() over (order by [Year])  from #Friday13s  group by [Year]  having count(*)=3),TripleFri13YearClusters as(  select FirstTriple=A.[Year]        ,SecondTriple=B.[Year]        ,ThirdTriple=C.[Year]        ,YearSpan        ,Ranking=rank() over (order by YearSpan)  from TripleFri13Years A  join TripleFri13Years B on A.RowNum+1=B.RowNum  join TripleFri13Years C on B.RowNum+1=C.RowNum  cross apply (select YearSpan=C.[Year]-A.[Year]+1) F1)select Century      ,Num333Clusters=count(*)from TripleFri13YearClusters cross apply (select Century=str(FirstTriple/100*100,4)+'s') F1where Ranking=1 and FirstTriple>=1800group by Centuryorder by Century/*Century Num333Clusters------- --------------1800s                41900s                32000s                42100s                42200s                42300s                32400s                42500s                42600s                42700s                32800s                42900s                4*/`
This current century has the most possible 3-3-3 Clusters as well!… There are going to be 4 of them. And it… Hold on a minute… Four 3-3-3 Clusters… Uh-oh… 4 + 3 + 3 + 3 = 13!

NOOOOO! It’s a sign! It looks like 2009 is the beginning of the end… Maybe the Mayans and Columbia Pictures are wrong about 2012 being the end of the world. All indicators point to NOW.

I don’t know about you, but now I’m really getting panicky… In fact, I think that…

OH NO!… IT CAN’T BE!

Are you sitting down?

Look at what happens if you add the ASCII values of the characters of the ominous string “FRIDAY THIRTEENTH 333 CLUSTER”:

`select SumOfAsciiChars=sum(ascii(substring(OminousString,Number,1)))from (select OminousString='FRIDAY THIRTEENTH 333 CLUSTER') Xjoin master..spt_values on Type='P' and Number between 1 and len(OminousString)/*SumOfAsciiChars---------------           2009*/`
It’s an omen! We’re doomed! MAKE IT STOP!! AAAAAIIIIIEEEEEEEE…

We regret to inform you that Mr Schulz has recently been admitted to the Institute for the Calendrically Insane. His planned release is on Friday, August 13th, 2010.

1. You be a strange little puppy. :)

2. @Brian:

Yeah... I'll probably never be invited to the T-SQL Tuesday Blog Party again.

Seriously, if's amazing what kind of junk you can find if you analyze data deeply enough.

3. Because I know you care, my birthday occurs on a Thursday most often. I have six more Thursday Birthdays than Monday Birthdays in the interval you mention!

By the way, bet you can't say Thursday Birthday ten times fast.

4. ... oh, and awesome post as always, I hope the asylum food is agreeable with you.

5. @Michael:

>
>I hope the asylum food is agreeable with you.
>

It was horrible... But luckily they let me out early.

>
>By the way, bet you can't say Thursday Birthday
>ten times fast.
>

Actually, I tried it and I CAN say it 10x real fast... However, my wife walked in the room when I was doing it and looked at me like I was an idiot.

>
>I have six more Thursday Birthdays than
>Monday Birthdays
>

Well, that's very interesting, because...

Wait a minute...

You dastardly fiend!!

Six
Plus
WeekDay Value of Thursday (5)
Plus
WeekDay Value of Monday (2)
Equals...

13!!!!

AAAAAAIIIIIIIEEEEEEE!

6. heh heh heh
*curls handlebar mustache*

7. I was just perusing the T-SQL Tuesday posts and came across yours. I have to say.... wow! Incredible. Great job.

8. @nullgarity:

Thanks! It always seems that the posts I think are the most outrageous are the ones that I get the best feedback on.

Thanks again...