代码之家 › 专栏 › 技术社区 › J. Michiels

SQL:在日期范围内每月生成记录

sql-server sql

J. Michiels · 技术社区 · 7 年前

我有一个表,它描述了一个在某段时间内有效的值。桌子看起来像这样:

+----+------------+------------+-------+
| Id |    From    |     To     | Value |
+----+------------+------------+-------+
|  1 | 2018-01-01 | 2018-03-31 | ValA  |
|  2 | 2018-01-16 | NULL       | ValB  |
|  3 | 2018-04-01 | 2018-05-12 | ValC  |
+----+------------+------------+-------+

如您所见,今天唯一仍然有效的值是valb(to可以为空,from不可以)。

我正试图在这张桌子上实现这样的视图(假设我在2018年7月的某一天呈现此视图):

+----------+------------+------------+-------+
| RecordId |    From    |     To     | Value |
+----------+------------+------------+-------+
|        1 | 2018-01-01 | 2018-01-31 | ValA  |
|        1 | 2018-02-01 | 2018-02-28 | ValA  |
|        1 | 2018-03-01 | 2018-03-31 | ValA  |
|        2 | 2018-01-16 | 2018-01-31 | ValB  |
|        2 | 2018-02-01 | 2018-02-28 | ValB  |
|        2 | 2018-03-01 | 2018-03-31 | ValB  |
|        2 | 2018-04-01 | 2018-04-30 | ValB  |
|        2 | 2018-05-01 | 2018-05-31 | ValB  |
|        2 | 2018-06-01 | 2018-06-30 | ValB  |
|        3 | 2018-04-01 | 2018-04-30 | ValC  |
|        3 | 2018-05-01 | 2018-05-12 | ValC  |
+----------+------------+------------+-------+

此视图基本上为表中的每条记录创建一条记录,但使用正确的日期(特别是注意不在月的第一天或最后一天的开始日期和结束日期)按月份分割。没有截止日期的一个记录(因此它仍然有效到今天)被呈现到我呈现视图的月份的最后一天,因此在撰写本文时,这是2018年7月。

这是一个简单的例子,但一个解决方案将真正帮助我。我需要这个来进行多次计算,包括按比例分配金额。

下面是一个表脚本和一些可以使用的insert语句:

CREATE TABLE [dbo].[Test]
(
    [Id] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [From] SMALLDATETIME NOT NULL,
    [To] SMALLDATETIME NULL,
    [Value] NVARCHAR(100) NOT NULL
)

INSERT INTO dbo.Test ([From],[To],[Value])
VALUES 
('2018-01-01','2018-03-31','ValA'),
('2018-01-16',null,'ValB'),
('2018-04-01','2018-05-12','ValC');

提前谢谢!

4 回复 | 直到 7 年前

EzLo tumao kaixin 7 年前

生成可能出现在您的值上的所有月份(包括开始和结束),然后在每个月与您的值的周期重叠的地方连接。更改结果,以便如果一个月没有完全重叠,则只显示周期的限制。

DECLARE @StartDate DATE = '2018-01-01'
DECLARE @EndDate DATE = '2020-01-01'

;WITH GeneratedMonths AS
(
    SELECT
        StartDate = @StartDate,
        EndDate = EOMONTH(@StartDate)
    UNION ALL
    SELECT
        StartDate = DATEADD(MONTH, 1, G.StartDate),
        EndDate = EOMONTH(DATEADD(MONTH, 1, G.StartDate))
    FROM
        GeneratedMonths AS G
    WHERE
        DATEADD(MONTH, 1, G.StartDate) < @EndDate
)
SELECT
    T.Id,
    [From] = CASE WHEN T.[From] >= G.StartDate THEN T.[From] ELSE G.StartDate END,
    [To] = CASE WHEN G.EndDate >= T.[To] THEN T.[To] ELSE G.EndDate END,
    T.Value
FROM
    dbo.Test AS T
    INNER JOIN GeneratedMonths AS G ON
        G.EndDate >= T.[From] AND
        G.StartDate <= ISNULL(T.[To], GETDATE())
ORDER BY
    T.Id,
    G.StartDate
OPTION
    (MAXRECURSION 3000)

Ajay Gupta 7 年前

使用日期函数和递归CTE。

with cte as
(
Select Id, Cast([From] as date) as [From], EOMONTH([from]) as [To1],
COALESCE([To],EOMONTH(GETDATE())) AS [TO],Value from test

UNION ALL

Select Id, DATEADD(DAY,1,[To1]), 
CASE when EOMONTH(DATEADD(DAY,1,[To1])) > [To] THEN CAST([To] AS DATE)
ELSE EOMONTH(DATEADD(DAY,1,[To1])) END as [To1], 
[To],Value from cte where TO1 <>  [To]
)
Select Id, [From],[To1] as [To], Value from cte order by Id

Yogesh Sharma 7 年前

@你的解决方案是好的,但需要设置两个固定值的变量。为了避免这种情况,您可以对实际数据执行递归cte。

WITH A AS(
    SELECT
    T.Id, CAST(T.[From] AS DATE) AS [From], CASE WHEN T.[To]<EOMONTH(T.[From], 0) THEN T.[To] ELSE EOMONTH(T.[From], 0) END AS [To], T.Value, CAST(0 AS INTEGER) AS ADD_M
    FROM
    TEST T
    UNION ALL
    SELECT
    T.Id, DATEADD(DAY, 1, EOMONTH(T.[From], -1+(A.ADD_M+1))), CASE WHEN T.[To]<EOMONTH(T.[From], A.ADD_M+1) THEN T.[To] ELSE EOMONTH(T.[From], A.ADD_M+1) END AS [To], T.Value, A.ADD_M+1
    FROM 
    TEST T
    INNER JOIN A ON T.Id=A.Id AND DATEADD(MONTH, A.ADD_M+1, T.[From]) < CASE WHEN T.[To] IS NULL THEN CAST(GETDATE() AS DATE) ELSE T.[To] END

) 
SELECT
    A.[Id], A.[From], A.[To], A.[Value]
FROM
    A
ORDER BY A.[Id], A.[From]

Yogesh Sharma 7 年前

递归的 CTE公司 如果没有大型数据集,则非常简单:

with t as (
    select id, [from], [to], Value
    from Test
    union all
    select id, dateadd(mm, 1, [from]), [to], value
    from t
    where dateadd(mm, 1, [from]) < coalesce([to], getdate())
)

select id, [from], (case when eomonth([from]) <= coalesce([to], cast(getdate() as date)) 
                         then eomonth([from]) else coalesce([to], eomonth([from])) 
                    end) as [To], 
       Value
from t
order by id;