Azure Synapse Analytics中的日期递归查询

在下表中，如何在Start_Date和End_Date列之间插入年份的第一个和最后一个日期的行？

EMPID	名称	开始日期	结束日期
1001	Shivansh	2015-09-01	2018-03-31
1004	Mayank	2019-04-01	2020-06-30

输出应如下所示：

EMPID	名称	开始日期	结束日期
1001	Shivansh	2015-09-01	2015-12-31
1001	Shivansh	2016-01-01	2016-12-31
1001	Shivansh	2017-01-01	2017-12-31
1001	Shivansh	2018-01-01	2018-03-31
1004	Mayank	2019-04-01	2019-12-31
1004	Mayank	2020-01-01	2020-06-30

这必须使用循环实现，因为Azure Synapse Analytics不支持递归公用表表达式

---

解决方案

此方法使用一个数字表和许多在Azure Synapse Analytics专用SQL池中可用的日期函数，包括DATEFROMPARTS、DATEDIFF和YEAR。

NB这不是递归查询。在创建Numbers表时使用了一个循环，但这只执行一次。一旦存在Numbers表，它就可以用于类似的方案，例如将递归CTE转换为与Azure Synapse Analytics兼容的基于集合的方法。

DATEFROMPARTS用于构造计算记录中一年的第一天。然后我使用DATEADD添加一年，然后去掉一天，得到一年的最后一天。DATEDIFFWith Year用于确定两个日期之间的年份间隔，从而确定需要添加的记录数。然后UNION完整结果的原始记录和计算记录。

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
GO

CREATE TABLE #tmp ( 
    empId       INT NOT NULL, 
    empName     VARCHAR(50) NOT NULL,
    start_date  DATE NOT NULL, 
    end_date    DATE NULL
)
GO


-- Setup test data
INSERT INTO #tmp ( empId, empName, start_date, end_date )
SELECT 1001, 'Shivansh', '2015-09-01', '2018-03-31'
UNION ALL
SELECT 1004, 'Mayank', '2019-04-01', '2020-06-30'
GO

;WITH cte AS (
SELECT *,
    DATEFROMPARTS( YEAR(start_date) + n.number, 1, 1 ) newStart
FROM #tmp t
    CROSS JOIN dbo.numbers n
WHERE n.number <= DATEDIFF( year, start_date, end_date ) 
)
SELECT 'o' s, empId, empName, start_date, 
    CASE
        WHEN YEAR(start_date) = YEAR(end_date) THEN end_date
        ELSE DATEFROMPARTS( YEAR(start_date), 12, 13 )
    END end_date
FROM #tmp
UNION ALL
SELECT 'c', empId, empName, 
    newStart AS start_date,
    CASE 
        WHEN YEAR(end_date) = YEAR(newStart) THEN end_date
        ELSE DATEADD( day, -1, DATEADD( year, 1, newStart ) ) 
    END newEnd
FROM cte
ORDER BY empId, start_date

我的结果：

我添加了o和c来表示原始行和计算行，但如果愿意，您可以删除该列。如果您还没有数字表格，那么我用来创建这个表格的脚本是here。此代码已在Azure Synapse Analytics专用SQL池上进行了测试，版本为Microsoft Azure SQL数据仓库-10.0.15554.0 Dec 10 2020 03：11：10。