Fun with SQL - Silver Pockets Full

Silver Pockets Full, send this message to your friends and in four days the money will surprise you. If you don't, well, a pox on your house. Or something like that. I didn't know what it was, I just saw this in my FB feed:



Back in November, I checked to see the frequency of having incremental numbers in the date, like 11/12/13 (my birthday) and 12/13/14 (kate's birthday). I don't want to hear how the rest of the world does their dates either, I know (I now write my dates like YYYY/MM/DD on everything, just so you know, that way I can sort it...or something).

Anyway, SQL to test out the claim of once every 823 years. Yay SQL.

OK, I'm not going to go into the steps necessary because I'm lazy (and I'm just lucky to be writing here), so here it is:

select *
from
  (
    select 
      to_char( d, 'yyyymm' ) year_month,
      count( case
               when to_char( d, 'fmDay' ) = 'Saturday' then 1
               else null
             end ) sats,
      count( case
               when to_char( d, 'fmDay' ) = 'Sunday' then 1
               else null
             end ) suns,
      count( case
               when to_char( d, 'fmDay' ) = 'Friday' then 1
               else null
             end ) fris
    from
      (
        select to_date( 20131231, 'yyyymmdd' ) + rownum d
        from dual
          connect by level <= 50000
      )
    group by 
      to_char( d, 'yyyymm' )
  )
where fris = 5
  and sats = 5
  and suns = 5

So over the next 50,000 days, this happens 138 times. I'm fairly certain that doesn't rise to the once every 823 years claim. But it's cool, maybe.

YEAR_MONTH       SATS       SUNS       FRIS
---------- ---------- ---------- ----------
201408              5          5          5 
201505              5          5          5 
201601              5          5          5 
201607              5          5          5 
201712              5          5          5 
128 more occurrences...
214607              5          5          5 
214712              5          5          5 
214803              5          5          5 
214908              5          5          5 
215005              5          5          5 

 138 rows selected 

I'm not the only dork that does this either, here's one in perl. I'm sure there are others, but again, I'm lazy.