SQL server 构建月份表

潇锐killer

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

阅读量273

点赞数 3

CC 4.0 BY-SA版权

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/wangqiaowq/article/details/138175508

本文介绍了一个SQL查询脚本，用于创建一个临时表，按月填充从开始日期到结束日期之间的财务年度季度月度数据，最后将结果插入到dim_finance_year_quarter_month表中并删除临时表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;