Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Friday the 13th Analysis in SQL Server

RSS
Modified on Wed, Feb 08, 2012, 2:09 PM by Administrator Categorized as SQL Server
declare @firstYear int = 1966
;
with digits as (
    select n=0
    union select 1
    union select 2
    union select 3
    union select 4
    union select 5
    union select 6
    union select 7
    union select 8
    union select 9
    )
,years as (
    select 
        n=a.n + b.n*10 + c.n*100 + @firstYear
    from
        digits a, digits b, digits c
    where c.n < 4
    )
,yearsExt as (
    select n, s=convert(varchar(4),n) from years
    )
,months as (
    select n=1
    union select 2
    union select 3
    union select 4
    union select 5
    union select 6
    union select 7
    union select 8
    union select 9
    union select 10
    union select 11
    union select 12
    )
,monthsExt as (
    select 
         n
        ,s = convert(varchar(2), n) 
    from 
        months
)
,dates as (
    select
         yr = y.n
        ,dt = CONVERT(date, y.s + '-' + m.s + '-13')
    from 
         yearsExt y
        ,monthsExt m
)
,datesExt as (
    select 
         *
        ,dow = datepart(weekday, dt)
        ,isFriday = case when datepart(weekday, dt) = 6 then 1 else 0 end
    from dates
)
,summary as (
    select
         yr
        ,FridayCount = sum(isFriday)
    from
        datesExt
    group by
        yr
    )
select
     FridayCount
    ,qty = count(1)
from 
    summary
group by 
    FridayCount
order by 
    FridayCount

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.