SQL server with方法修改
SQL server with方法修改，SQL 2008 不支持 with方法 需要改写下：
WITH MonthsCTE AS (
4 SELECT @StartDate AS MonthStart
5 UNION ALL
6 SELECT DATEADD(month, -1, MonthStart)
7 FROM MonthsCTE
8 WHERE MonthStart > @EndDate
9 )
10
11 INSERT INTO dbo.dim_year_quarter_month ([dt],[year],[quarter],[ym])
12 SELECT
13 @DateThreshold as dt,
14 DATEPART(YEAR, MonthStart) AS year,
15 DATEPART(QUARTER, MonthStart) AS quarter,
16 CONVERT(varchar(7), MonthStart, 120) as ym
17 FROM MonthsCTE
18 WHERE MonthStart <= @StartDate
19 ORDER BY MonthStart DESC改为
-- 创建临时表存储结果
CREATE TABLE #TempMonths (
MonthStart DATE
)
-- 使用WHILE循环填充临时表
WHILE @CurrentDate >= @EndDate
BEGIN
INSERT INTO #TempMonths (MonthStart)
VALUES (@CurrentDate);
SET @CurrentDate = DATEADD(month, -1, @CurrentDate);
END
-- 插入新数据
INSERT INTO dbo.dim_year_quarter_month([dt], [year], [quarter], [ym])
SELECT
@DateThreshold as dt,
DATEPART(YEAR, tm.MonthStart) AS year,
DATEPART(QUARTER, tm.MonthStart) AS quarter,
CONVERT(varchar(7), tm.MonthStart, 120) as ym
FROM #TempMonths tm
WHERE tm.MonthStart <= @StartDate
ORDER BY tm.MonthStart DESC
-- 清理临时表
DROP TABLE #TempMonths