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…



Friday The ThirteenthIs 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 #Calendar
go
with
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 #Calendar
from Numbers
cross apply (select [Date]=convert(datetime,dateadd(day,Num-1,'17530101'))) F1
where 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 #Calendar
where [Day]=13
group by DayOfWeek
order by NumOccurrences desc
/*
DayOfWeek NumOccurrences
--------- --------------
Friday 2146
Wednesday 2142
Sunday 2142
Tuesday 2137
Monday 2133
Saturday 2132
Thursday 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'
end
from
HighestOccurrences
order by [Day],DayOfWeek
/*
MostFrequentlyOccurringDay
--------------------------
Sunday the 1st
Monday the 2nd
Tuesday the 3rd
...
Wednesday the 11th
Thursday the 12th
Friday the 13th
Saturday the 14th
Sunday the 15th
...
Thursday the 26th
Friday the 27th
Saturday 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 #Friday13s
go
select [Date]
,[Year]
,[Month]
into #Friday13s
from #Calendar
where 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 #Friday13s
cross apply (select Century=str([Year]/100*100,4)+'s') F1
where [Year]>=1800
group by Century
order by Century
/*
Century NumOccurrences
------- --------------
1800s 172
1900s 172
2000s 172
2100s 172
2200s 172
2300s 172
2400s 172
2500s 172
2600s 172
2700s 172
2800s 172
2900s 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 #Friday13s
where [Year]=2009
order by [Date]
/*
Date
-----------------------
2009-02-13 00:00:00.000
2009-03-13 00:00:00.000
2009-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]
,NumOccurrences
from HighestOccurrences
order by [Year]
/*
Year NumOccurrences
---- --------------
1761 3
1764 3
1767 3
...
1931 3
1942 3
1953 3
1956 3
1959 3
1970 3
1981 3
1984 3
1987 3
1998 3
2009 3
2012 3
2015 3
2026 3
2037 3
2040 3
2043 3
...
2989 3
2992 3
2995 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
,YearSpan
from 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') F1
where Ranking=1 and FirstTriple>=1800
group by Century
order by Century
/*
Century Num333Clusters
------- --------------
1800s 4
1900s 3
2000s 4
2100s 4
2200s 4
2300s 3
2400s 4
2500s 4
2600s 4
2700s 3
2800s 4
2900s 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') X
join 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.

8 comments:

  1. @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.

    --Brad

    ReplyDelete
  2. 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.

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

    ReplyDelete
  4. @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!

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

    ReplyDelete
  6. @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...

    --Brad

    ReplyDelete