从TFACS SAP日历创建SSMS中的工作日历

Question

我有一个SAP配置的TFACS日历源表，格式如下：

​

​

这是源表。这是一个日历表，月份列中的每个"1或0“表示一天，1表示工作天数，0表示根据美国假日日历计算的每年未工作的天数

我希望把这个表格转换成这样的格式：

​

​

整个桌子。

有没有人知道在Server环境中有什么方法可以做到这一点？

Answer 1

最后我得到了这个，它解决了这个案子。

  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)

Answer 2

最简单的方法是加入日历表。

然后使用月中的一天从月份字符串中提取0/1。

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;

在这个示例查询中创建REF_CALENDAR表可以在中找到。