SQL server 构建月份表

潇锐killer

于 2024-04-25 09:07:39 发布

阅读量112

点赞数 3

文章标签：数据库

本文链接：https://blog.csdn.net/wangqiaowq/article/details/138175508

版权

DECLARE @CurrentMonthStart DATE = @StartDate;
DECLARE @PreviousMonthStart DATE;

BEGIN TRANSACTION;

CREATE TABLE #TempMonths (MonthStart DATE);

WHILE @CurrentMonthStart >= @EndDate
BEGIN
    INSERT INTO #TempMonths (MonthStart) VALUES (@CurrentMonthStart);
    
    SET @PreviousMonthStart = @CurrentMonthStart;
    SET @CurrentMonthStart = DATEADD(month, -1, @CurrentMonthStart);
END

COMMIT TRANSACTION;

INSERT INTO dbo.dim_finance_year_quarter_month ([dt], [year], [quarter], [ym])
SELECT 
    @DateThreshold AS dt,
    DATEPART(YEAR, MonthStart) AS year,
    DATEPART(QUARTER, MonthStart) AS quarter,
    CONVERT(varchar(7), MonthStart, 120) AS ym
FROM #TempMonths
WHERE MonthStart <= @StartDate
ORDER BY MonthStart DESC;

DROP TABLE #TempMonths;