我有一個 SAP 配置的TFACS日歷源表，格式如下：
這是源表。這是一個日歷表，Month 列中的每個“1 或 0”代表一天，1 表示作業天數，0 表示每年不作業天數，基于美國假期日歷
我希望將此表轉換為這種格式：
對于整個表。
有人知道在 SQL Server 環境中完成此任務的任何方法嗎？
uj5u.com熱心網友回復：
最簡單的方法是加入日歷表。
然后使用月份中的日期從月份字串中提取 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 表可以在這個舊的 SO 帖子中找到。
uj5u.com熱心網友回復：
我最終解決了這個問題，它解決了這個問題。
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)
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/405455.html
標籤：