-- ==============================================================================================
-- File: dim_day_fiscal_sql.txt
-- Purpose: To extend and populate the bulk of the fiscal calendar columns of the dim_day table
-- with the US Federal fiscal calendar periods (SQL Server ONLY)
-- An original work developed and published by Don McMunn 
-- Last revised: 10-Jan-2008
---
-- Don McMunn is a professional business intelligence and data warehousing consultant
-- with over 15 years of experience and may be reached at dmcmunn at ipcdesigns dot com
-- ==============================================================================================

-- assumes the non-fiscal columns have already been populated in dim_day
-- add additional columns to dim_day for fiscal day of year, quarter, month and week

-- establish fiscal year for US Federal calendar (October 1 - September 30)
update  dim_day
set fiscal_year_num = case when month(full_date) < 10 then year(full_date) else year(full_date) + 1 end 
from dim_day
where fiscal_year_num is null
and full_date is not null
go

-- populate the fiscal year begin/end dates
update d
set 
  d.fiscal_year_begin_date = s.fiscal_year_begin_date
, d.fiscal_year_end_date = s.fiscal_year_end_date
from dim_day d
inner join 
(
select 
  fiscal_year_num
-- For the purists...
-- if you want to introduce dates NOT in your table that you know to be "fiscally correct",
-- you could substitute the case statements below vs. using straight min/max full_date
-- -------------------------------------------------------------------------------------
--  NOTE: If you decide to do this, I would recommend populating your dim_day table 
--  with all dates in 1899 between Oct and Dec and in 2501 between Jan - Sep 
--  or better yet...add each day of these years! 
--  to maintain the highest integrity of your data  -- FWIW dm
-- -------------------------------------------------------------------------------------
-- , case when max(year(full_date)) = max(year_limits.min_year) and avg(month(full_date)) < 10 then cast(cast(max(year_limits.min_year) - 1 as varchar(4)) + '-10-01' as datetime) else min(full_date) end  as fiscal_year_begin_date
-- , case when max(year(full_date)) = max(year_limits.max_year) and avg(month(full_date)) >= 10 then cast(cast(max(year_limits.max_year) + 1 as varchar(4)) + '-09-30' as datetime) else max(full_date) end as fiscal_year_end_date
, min(full_date) as fiscal_year_begin_date
, max(full_date) as fiscal_year_end_date
from dim_day
,
( -- dynamically determine the min/max year limits of the dim_day table 
  -- to be full joined back to the dim_day table 
select 
  year(min(full_date)) as min_year
, year(max(full_date)) as max_year 
from dim_day
) year_limits
group by fiscal_year_num
) s
on d.fiscal_year_num = s.fiscal_year_num
where 
(d.fiscal_year_begin_date is null
or d.fiscal_year_end_date is null)
and d.full_date is not null
go


-- populate fiscal year begin/end date key
update d
set 
  fiscal_year_begin_date_key = b.date_key
, fiscal_year_end_date_key = e.date_key
from dim_day d
inner join dim_day b
on d.fiscal_year_begin_date = b.full_date
inner join dim_day e
on d.fiscal_year_end_date = e.full_date
where d.full_date is not null
and (d.fiscal_year_begin_date_key is null
or d.fiscal_year_end_date_key is null)
go

-- populate last day of fiscal year indicator
update dim_day
set is_last_day_of_fiscal_year = case when date_key = fiscal_year_end_date_key then 'Y' else 'N' end
where full_date is not null
go


-- populate fiscal month begin/end dates from normal months for US Federal fiscal calendar
update dbo.dim_day
set 
  fiscal_month_begin_date = month_begin_date
, fiscal_month_end_date = month_end_date
go

-- populate fiscal year begin/end date key
update d
set 
  fiscal_month_begin_date_key = b.date_key
, fiscal_month_end_date_key = e.date_key
from dim_day d
inner join dim_day b
on d.fiscal_month_begin_date = b.full_date
inner join dim_day e
on d.fiscal_month_end_date = e.full_date
where d.full_date is not null
and (d.fiscal_month_begin_date_key is null
or d.fiscal_month_end_date_key is null)
go


update dim_day
set is_last_day_of_fiscal_month = 'N'
go

update dim_day
set is_last_day_of_fiscal_month
   = case when date_key = fiscal_month_end_date_key then 'Y' else 'N' end
where full_date is not null
go

-- populate fiscal month of year for US Federal fiscal calendar
update dbo.dim_day
set fiscal_month_num_of_year 
  = case month_num_of_year
       when 10 then  1 
       when 11 then  2
       when 12 then  3
       when  1 then  4
       when  2 then  5
       when  3 then  6
       when  4 then  7
       when  5 then  8
       when  6 then  9
       when  7 then 10
       when  8 then 11
       when  9 then 12
    end
where full_date is not null
and fiscal_month_num_of_year is null
go


-- populate the fiscal quarter of the year for US Federal fiscal calendar
update dbo.dim_day
set fiscal_quarter_num_of_year 
  = case when fiscal_month_num_of_year in (1,2,3) then 1 
         when fiscal_month_num_of_year in (4,5,6) then 2
         when fiscal_month_num_of_year in (7,8,9) then 3
         when fiscal_month_num_of_year in (10,11,12) then 4 
    end 
where full_date is not null
and fiscal_quarter_num_of_year is null
go


-- populate fiscal quarter begin/end dates
update d
set fiscal_quarter_begin_date = s.fiscal_quarter_begin_date
  , fiscal_quarter_end_date = s.fiscal_quarter_end_date
from dbo.dim_day d
inner join
(
 select fiscal_year_num
      , fiscal_quarter_num_of_year
      , min(full_date) as fiscal_quarter_begin_date
      , max(full_date) as fiscal_quarter_end_date
 from dbo.dim_day s
 group by fiscal_year_num, fiscal_quarter_num_of_year
) s
on d.fiscal_quarter_num_of_year = s.fiscal_quarter_num_of_year
and d.fiscal_year_num = s.fiscal_year_num
where d.full_date is not null
go


-- populate fiscal quarter begin/end date key
update d
set 
  fiscal_quarter_begin_date_key = b.date_key
, fiscal_quarter_end_date_key = e.date_key
from dim_day d
inner join dim_day b
on d.fiscal_quarter_begin_date = b.full_date
inner join dim_day e
on d.fiscal_quarter_end_date = e.full_date
where d.full_date is not null
and (d.fiscal_quarter_begin_date_key is null
or d.fiscal_quarter_end_date_key is null)

alter table dim_day 
add fiscal_day_num_of_year int
, fiscal_day_num_of_quarter int
, fiscal_day_num_of_month int
, fiscal_day_num_of_week int
go



select 
  full_date
, datediff( day 
          , cast(
                  cast(
                       case when month(full_date) < 10 
                         then year(full_date)-1 
                         else year(full_date)
                       end 
                       as varchar(4)
                      ) + '-09-30' as datetime
                )
          , full_date 
          ) as days_since_1001
, (datediff( day 
          , cast(
                  cast(
                       case when month(full_date) < 10 
                         then year(full_date)-1 
                         else year(full_date)
                       end 
                       as varchar(4)
                      ) + '-09-30' as datetime
                )
          , full_date 
          ) + 1) / 7 as weeks_since_1001

 from dim_day
go

-- populate fiscal day number of year
update dim_day
set fiscal_day_num_of_year =
-- Assumes first year in dim_day begins January 1...and US Federal year begins October 1
-- are we in first year and a month less than the beginning of US Federal fiscal year?
-- yes: calculate fiscal day of year number from beginning of last day of previous year
-- no: calculate offset from 30-Sep of previous year
  case 
     when year(full_date) = (
                             select min(year(full_date)) 
                               from dim_day 
                              where full_date is not null
                            )
      and month(full_date) < 10
       then datediff( d
                    , cast(
                         cast(
                                (
                                 select min(year(full_date))-1
                                   from dim_day 
                                  where full_date is not null
                                ) as varchar(4)
-- Again, for the purists out there, if you must have symmetry...
-- uncomment the following line and comment out the 2nd line down
--                           ) + '-09-30' as datetime
                             ) + '-12-31' as datetime
                          )
                    , full_date)
     else datediff(d, cast(
                           cast(
                                 case 
                                   when month(full_date) < 10 
                                      then year(full_date) - 1 
                                      else year(full_date) 
                                 end as varchar(4)
                               ) + '-09-30' as datetime
                          )
                   , full_date )
  end 
from dim_day
where full_date is not null
and fiscal_day_num_of_year is null
go

-- populate fiscal week of year number
update dim_day
set fiscal_week_num_of_year = ((fiscal_day_num_of_year - 1) / 7) + 1 
where full_date is not null
go


-- populate fiscal week begin/end dates
update d
set fiscal_week_begin_date = s.fiscal_week_begin_date
, fiscal_week_end_date = s.fiscal_week_end_date
from dim_day d
inner join 
(
select
  fiscal_year_num
, fiscal_week_num_of_year
, min(full_date) as fiscal_week_begin_date
, max(full_date) as fiscal_week_end_date
from dim_day
where full_date is not null
group by fiscal_year_num, fiscal_week_num_of_year
) s
on d.fiscal_year_num = s.fiscal_year_num
and d.fiscal_week_num_of_year = s.fiscal_week_num_of_year
where s.full_date is not null
go

-- populate fiscal week begin/end key values
update d
set 
  fiscal_week_begin_date_key = b.date_key
, fiscal_week_end_date_key = e.date_key
from dim_day d
inner join dim_day b
on d.fiscal_week_begin_date = b.full_date
inner join dim_day e
on d.fiscal_week_end_date = e.full_date
where d.full_date is not null
and (d.fiscal_week_begin_date_key is null
or d.fiscal_week_end_date_key is null)
go

-- populate fiscal day of quarter, month and week
update dim_day 
set
  fiscal_day_num_of_quarter = datediff(d, fiscal_quarter_begin_date - 1, full_date)
, fiscal_day_num_of_month = datediff(d, fiscal_month_begin_date - 1, full_date)
, fiscal_day_num_of_week = datediff(d, fiscal_week_begin_date - 1, full_date)
where full_date is not null
go


-- ==============================================================================================
-- Don McMunn is a professional business intelligence and data warehousing consultant
-- with over 15 years of experience and may be reached at dmcmunn at ipcdesigns dot com
-- ==============================================================================================