时间戳

首页 > 解决方案 > SQL-Server 获取介于两个日期之间的每一天的数据

sql-server - SQL-Server 获取介于两个日期之间的每一天的数据

问题描述

我有一张工作表：

job_id  [unique ID]
pref_start [date]
spec_duration [time in seconds]

我可以从首选的开始和持续时间计算结束日期。pref_start 不是固定的，工程师可以随意更改。

我需要报告任何一周的活动，所以如果我有类似的数据：

jid   start        end 
J1    01/01/yyyy   15/02/yyyy
J2    07/01/yyyy   08/02/yyyy
J3    09/02/yyyy   21/03/yyyy

我将如何查询“告诉我从 07/02/yyyy 到 12/02/yyyy 一周中的每一天发生的工作 ID”

标签： sql-serverdate

解决方案

首先找到您的作业和过滤间隔之间的匹配间隔，然后过滤间隔和重叠间隔的天数必须匹配：

DECLARE @Jobs TABLE (
    ID INT IDENTITY, 
    StartDate DATE,
    EndDate DATE)

INSERT INTO  @Jobs (
    StartDate,
    EndDate)
VALUES
    ('2019-01-01', '2019-02-15'),
    ('2019-01-07', '2019-02-08'),
    ('2019-02-09', '2019-03-21')


DECLARE @FilterStartDate DATE = '2019-02-07'
DECLARE @FilterEndDate DATE = '2019-02-12'


;WITH AtLeast1DayOverlappingJobs AS
(
    SELECT
        J.ID,
        J.StartDate,
        J.EndDate,
        OverlappingStartDate = CASE
            WHEN J.StartDate > @FilterStartDate THEN J.StartDate ELSE @FilterStartDate END, -- Highest of 2
        OverlappingEndDate = CASE
            WHEN J.EndDate < @FilterEndDate THEN J.EndDate ELSE @FilterEndDate END -- Lowest of 2
    FROM
        @Jobs AS J
    WHERE
        -- They share at least 1 day
        @FilterStartDate <= J.EndDate AND @FilterEndDate >= J.StartDate
)
SELECT
    T.*
FROM
    AtLeast1DayOverlappingJobs AS T
WHERE
    -- Amount of days must match between filter and overlapping periods
    DATEDIFF(DAY, @FilterStartDate, @FilterEndDate) = DATEDIFF(DAY, T.OverlappingStartDate, T.OverlappingEndDate)

结果：

ID  StartDate   EndDate     OverlappingStartDate    OverlappingEndDate
1   2019-01-01  2019-02-15  2019-02-07              2019-02-12

推荐阅读