Create working calendar in SSMS from TFACS SAP calendar-CodePudding

I have a SAP-configured TFACS calendar source table in this format:

This is the source table. This is a calendar table, and each "1 or 0" in the Month column represents a day, and 1 is for days worked and 0 is for days not worked for each year based off of the US holiday Calendar

I am looking to transform this table into this format:

For the entire table.

Does anybody know of any way to accomplish this in SQL Server environment?

CodePudding user response：

The easiest way is to join with a calendar table.
Then use the day of the month to extract the 0/1 from the month strings.

select 
  cal_year as [Year]
, cal_month as [Month]
, cal_day as [Day]
, try_cast(case cal_month
  when 1 then substring(Mon1, cal_day, 1)
  when 2 then substring(Mon2, cal_day, 1)
  when 3 then substring(Mon3, cal_day, 1)
  when 4 then substring(Mon4, cal_day, 1)
  when 5 then substring(Mon5, cal_day, 1)
  when 6 then substring(Mon6, cal_day, 1)
  when 7 then substring(Mon7, cal_day, 1)
  when 8 then substring(Mon8, cal_day, 1)
  when 9 then substring(Mon9, cal_day, 1)
  when 10 then substring(Mon10, cal_day, 1)
  when 11 then substring(Mon11, cal_day, 1)
  when 12 then substring(Mon12, cal_day, 1)
  end as tinyint) as [Working Day]
from your_tfacs_calendar t
join ref_calendar cal 
  on cal.cal_year = t.year;

The creation of the REF_CALENDAR table in this example query can be found in this old SO post.

CodePudding user response：

I ended up with this, it solves the case.

  with calendar as
  ( select cast('2005-01-01' as date) as [Date], 2022 as [Year], 1 as [Month], 1 
  as [Day]
  union all
  select dateadd(day,1,[Date]), DATEPART(year,dateadd(day,1,[Date])), 
  DATEPART(MONTH,dateadd(day,1,[Date])), DATEPART(DAY,dateadd(day,1,[Date])) 
  from calendar
  where [Date] <= '2030-12-31'
  )

  select c.*,
  CASE c.month when 1 then SUBSTRING(t.TFACS_MON01,c.Day,1)
                                                        when 2 then 
  SUBSTRING(t.TFACS_MON02,c.Day,1)
                                                        when 3 then 
  SUBSTRING(t.TFACS_MON03,c.Day,1)
                                                        when 4 then 
  SUBSTRING(t.TFACS_MON04,c.Day,1)
                                                        when 5 then 
  SUBSTRING(t.TFACS_MON05,c.Day,1)
                                                        when 6 then 
  SUBSTRING(t.TFACS_MON06,c.Day,1)
                                                        when 7 then 
  SUBSTRING(t.TFACS_MON07,c.Day,1)
                                                        when 8 then 
  SUBSTRING(t.TFACS_MON08,c.Day,1)
                                                        when 9 then 
  SUBSTRING(t.TFACS_MON09,c.Day,1)
                                                        when 10 then 
  SUBSTRING(t.TFACS_MON10,c.Day,1)
                                                        when 11 then 
  SUBSTRING(t.TFACS_MON11,c.Day,1)
                                                        when 12 then 
  SUBSTRING(t.TFACS_MON12,c.Day,1) 
                    END as WorkDay

  from calendar c
  join [TFACS] t on t.TFACS_JAHR = c.Year

  ORDER BY Date

  OPTION(MAXRECURSION 30000)