时间戳

首页 > 解决方案 > 根据修改日期填写月份日期

sql - 根据修改日期填写月份日期

问题描述

我正在尝试生成名为月份的附加列并创建附加记录以填充全年的月份。

这个原始数据的例子

ID   | DateModified
---- | ------------
123  | 01-01-2020
123  | 11-05-2020

这是期望的结果

ID  | DateModified | Month
--- | ------------ | -----
123 | 01-01-2020   | Jan
123 | 01-01-2020   | Feb
123 | 01-01-2020   | March
123 | 01-01-2020   | April
123 | 11-05-2020   | May
123 | 11-05-2020   | June
123 | 11-05-2020   | July
123 | 11-05-2020   | August
123 | 11-05-2020   | September
123 | 11-05-2020   | October
123 | 11-05-2020   | November
123 | 11-05-2020   | December

请告知如何存档这些结果。

标签： sqlsql-server

解决方案

更新了答案。我希望这对你有用......请注意，它适用于同一年内的日期，而不是跨越到下一个。

CREATE TABLE wrk(
    id int,
    DateModified datetime
)
GO

INSERT INTO wrk values(123, '20200101')
INSERT INTO wrk values(123, '20200511')
GO

WITH
    months as(
        SELECT 1  AS month_idx, 'jan' AS month_key UNION ALL
        SELECT 2  AS month_idx, 'feb' AS month_key UNION ALL
        SELECT 3  AS month_idx, 'mar' AS month_key UNION ALL
        SELECT 4  AS month_idx, 'apr' AS month_key UNION ALL
        SELECT 5  AS month_idx, 'may' AS month_key UNION ALL
        SELECT 6  AS month_idx, 'jun' AS month_key UNION ALL
        SELECT 7  AS month_idx, 'jul' AS month_key UNION ALL
        SELECT 8  AS month_idx, 'aug' AS month_key UNION ALL
        SELECT 9  AS month_idx, 'sep' AS month_key UNION ALL
        SELECT 10 AS month_idx, 'oct' AS month_key UNION ALL
        SELECT 11 AS month_idx, 'nov' AS month_key UNION ALL
        SELECT 12 AS month_idx, 'dec' AS month_key
    ),
    w as(
        SELECT
            w.id, w.DateModified,
            --
            datepart(month, w.DateModified) AS MonthModifiedFrom,
            --
            isnull(
                datepart(
                    month,
                    min(w_next.DateModified)
                ) - 1,
                12 /* December */
            ) AS MonthModifiedTo
        FROM
            wrk w
                LEFT OUTER JOIN wrk w_next
                ON(
                        w_next.id = w.id
                    AND w_next.DateModified > w.DateModified
                )
        GROUP BY w.id, w.DateModified
    )
SELECT
    w.id, w.DateModified, m.month_key
FROM
    w
        INNER JOIN months m
        ON m.month_idx BETWEEN w.MonthModifiedFrom AND w.MonthModifiedTo

推荐阅读