时间戳

首页 > 解决方案 > 基于日历的行扩展

sql-server - 基于日历的行扩展

问题描述

我的事实表如下

declare @fact as TABLE (WO varchar(3), startYear int, startFiscalPeriod int, endFiscalPeriod int)
INSERT INTO @fact
Select WO, startYear,startFiscalPeriod, endFiscalPeriod
from
(
VALUES
('WO1', 2020, 202011, 202106),
('WO2', 2020, 202009, 202106),
('WO3', 2021, 202102, 202106)

) t (WO, startYear,startFiscalPeriod, endFiscalPeriod)
select * from @fact
WO 开始年 开始财政期 endFiscalPeriod
WO1 2020 202011 202106
WO2 2020 202009 202106
WO3 2021 202102 202106

我想根据 startFiscalPeriod 和 endFiscalPeriod 之间的间隔扩展行，如下所示

;with cte as
(select WO, startYear,startFiscalPeriod, endFiscalPeriod from @fact
UNION ALL
select WO, startYear,startFiscalPeriod+1,endFiscalPeriod from CTE
where startFiscalPeriod<[endFiscalPeriod])

但是，我希望基于 calendarTable 进行扩展。

    declare @Calendar as TABLE  (fiscalYear int, periodNumber int, fiscalPeriod int)
INSERT INTO @Calendar
Select fiscalYear, periodNumber,fiscalPeriod
from
(
VALUES 
(2020, 1, 202001), 
(2020, 2, 202002), 
(2020, 3, 202003), 
(2020, 4, 202004),
(2020, 5, 202005),
(2020, 6, 202006),
(2020, 7, 202007),
(2020, 8, 202008),
(2020, 9, 202009),
(2020, 10, 202010),
(2020, 11, 202011),
(2020, 12, 202012),
(2021, 1, 202101),
(2021, 2, 202102),
(2021, 3, 202103),
(2021, 4, 202104),
(2021, 5, 202105),
(2021, 6, 202106)
) t (fiscalYear, periodNumber,fiscalPeriod)

我想要的输出如下，我无法通过简单地扩展简单的 do while 逻辑上的行来生成。有没有办法根据日历来调整 do while 逻辑？

WO 开始年 开始财政期 endFiscalPeriod
WO1 2020 202011 202106
WO1 2020 202012 202106
WO1 2020 202101 202106
WO1 2020 202102 202106
WO1 2020 202103 202106
WO1 2020 202104 202106
WO1 2020 202105 202106
WO1 2020 202106 202106
WO2 2020 202009 202106
WO2 2020 202010 202106
WO2 2020 202011 202106
WO2 2020 202012 202106
WO2 2020 202101 202106
WO2 2020 202102 202106
WO2 2020 202103 202106
WO2 2020 202104 202106
WO2 2020 202105 202106
WO2 2020 202106 202106
WO3 2021 202102 202106
WO3 2021 202103 202106
WO3 2021 202104 202106
WO3 2021 202105 202106
WO3 2021 202106 202106

先感谢您

标签： sql-servertsql

解决方案

为什么不使用 between 条件连接两个表，例如Calendar.fiscalPeriod between fact.startFiscalPeriod and fact.endFiscalPeriod？

select
    f.WO
    , f.startYear
    , c.fiscalPeriod startFiscalPeriod
    , f.endFiscalPeriod  
from
    @fact f
    inner join @Calendar c on c.fiscalPeriod between f.startFiscalPeriod and f.endFiscalPeriod
order by
    f.WO
    , c.fiscalPeriod

推荐阅读