'How can i implement the recursive CTE in Azure Synapse
help me with converting the below nested CTE to a normal query or how this would work in Azure Synapse
help me with converting the below nested CTE to a normal query or how this would work in Azure Synapse
The Sample code reads like below
`
;WITH CTE_AS
AS
(
SELECT DISTINCT
SKY,
[Empl ID]
#
FROM #temp
) select * from CTE_AS;
CTE_APD
AS
(
SELECT
PdATE = @startDate
UNION ALL
SELECT
PdATE = DATEADD(MONTH, 1, PdATE)
FROM CTE_APD
WHERE DATEADD(MONTH, 1, PdATE) <= @endDate
),
CTE_ASP
AS
(
SELECT
SKY = CTE_AS.SKY,
[Empl ID] = CTE_AS.[Empl ID],
PeriodID = YEAR(CTE_APD.PdATE) * 100 + MONTH(CTE_APD.PdATE),
[FY] =
CAST(
CASE
WHEN MONTH(CTE_APD.PdATE) BETWEEN 4 AND 12
THEN YEAR(CTE_APD.PdATE) + 1
ELSE YEAR(CTE_APD.PdATE)
END
AS VARCHAR(4)),
QTD =
CASE
WHEN MONTH(CTE_APD.PdATE) BETWEEN 4 AND 6 THEN 'Q1'
WHEN MONTH(CTE_APD.PdATE) BETWEEN 7 AND 9 THEN 'Q2'
WHEN MONTH(CTE_APD.PdATE) BETWEEN 10 AND 12 THEN 'Q3'
ELSE 'Q4'
END
FROM CTE_APD
JOIN CTE_AS ON 1 = 1
),
CTE_MinFYPeriod
AS
(
SELECT
SKY,
[ ID],
FY,
MinPeriodID = MIN(PeriodID)
FROM #TEMP
GROUP BY SKY, FY, [Empl ID]
)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
