SQL 服务器日期范围计算

Question

我有如下表。

Workid  StartDate   EndDate
101     2012-03-01  2013-03-20
101     2013-03-21  2014-08-01
102     2014-01-01  2014-12-31
103     2012-01-01  2012-10-11
103     2012-10-12  2014-12-31
104     2012-01-01  2012-12-25
105     2012-01-01  2014-06-30
105     2014-07-01  2014-12-31

我需要验证给定年份的工作ID是否完全占用。例如，如果我选择年份为 2012，则输出应如下所示。

WorkID  STATUS
101     Not Occupied
102     Not Occupied
103     Occupied
104     Not Occupied
105     Occupied

如果我选择年份作为 2013 年，那么输出应该如下所示。

WorkID  STATUS
101     Occupied
102     Not Occupied
103     Occupied
104     Not Occupied
105     Occupied

有人可以帮忙吗？

下面是示例表脚本。

CREATE TABLE #temp
(
Workid INT,
StartDate DATE,
EndDate DATE
)
go
INSERT INTO #temp
(
Workid,
StartDate,
EndDate
)
VALUES
(101, '2012-03-01', '2013-03-20'),
(101, '2013-03-21', '2014-08-01'),
(102, '2014-01-01', '2014-12-31'),
(103, '2012-01-01', '2012-10-11'),
(103, '2012-10-12', '2014-12-31'),
(104, '2012-01-01', '2012-12-25'),
(105, '2012-01-01', '2014-06-30'),
(105, '2014-07-01', '2014-12-31')
;

Answer 1

您可以使用递归 cte 查找每个工作 ID 的日期范围并比较这些日期范围，这也将考虑工作中的差异(演示：http://rextester.com/NUEW6510(

DECLARE @year INT = 2013;
WITH calendarSearch (Workid,StartDate, EndDate, StartDate2, EndDate2) AS
(
SELECT Workid,StartDate, EndDate, CAST('1900-01-01' AS DATE), CAST('1900-01-01' AS DATE)
FROM #temp
UNION ALL
SELECT c.Workid, c.StartDate, t.EndDate, t.StartDate, c.EndDate
FROM #temp t
JOIN calendarSearch c
ON c.Workid = t.Workid
AND t.EndDate > c.EndDate
AND t.StartDate <= DATEADD(DAY,1,c.EndDate)
), occupiedRange AS
(
SELECT Workid,StartDate, EndDate FROM calendarSearch c
WHERE c.StartDate NOT IN (SELECT StartDate2 FROM calendarSearch WHERE calendarSearch.Workid = c.Workid)
AND c.EndDate NOT IN (SELECT EndDate2 FROM calendarSearch WHERE calendarSearch.Workid = c.Workid)
)
SELECT Workid, 
CASE WHEN EXISTS (SELECT 1 FROM occupiedRange WHERE Workid = o1.Workid and StartDate <= datefromparts(@year,1,1) AND EndDate >= DATEFROMPARTS(@year,12,31)) THEN 'Occupied' ELSE 'Not Occupied' END AS Status
FROM occupiedRange o1
GROUP BY o1.Workid
ORDER BY Workid

Answer 2

使用以下查询

select b.WORKID, (case when b.SD_COUNT > 0 and b.ED_COUNT > 0 then 'Occupied' else 'Not Occupied' end ) as STATUS from 
(select a.Workid as WORKID, Sum(a.Status_StartDate) as SD_COUNT, Sum(a.Status_EndDate) as ED_COUNT from 
(Select Workid, (case when StartDate <= '2012-01-01' then 1 else 0 end as Status_StartDate), (case when EndDate >= '2012-12-31' then 1 else 0 end) as Status_EndDate from tablename)a group by a.Workid)b;

Answer 3

这花了我一段时间，但查询会起作用。可能有更好的方法，但正如我所说，这确实有效。测试一下，祝你好运！

USE tempdb
DECLARE @StartDate DATE
DECLARE @EindDate DATE
SET @StartDate = '1990-01-01'  -- << user input >> --
SET @EindDate  = '2018-12-31'  -- << user input >> --
IF OBJECT_ID ('TEMPDB..#Date') IS NOT NULL DROP TABLE #Date
IF OBJECT_ID ('TEMPDB..#Date') IS NULL CREATE TABLE #Date (Date_ DATE)
INSERT INTO #Date VALUES (@StartDate)
WHILE @StartDate < @EindDate
BEGIN
INSERT INTO #Date
SELECT DATEADD (DD, 1, @StartDate) AS Date
SET @StartDate = DATEADD (DD, 1, @StartDate)
END;

SELECT * FROM #Date
CREATE TABLE #temp
(
Workid INT,
StartDate DATE,
EndDate DATE
)
GO
INSERT INTO #temp
(
Workid,
StartDate,
EndDate
)
VALUES
(101, '2012-03-01', '2013-03-20'),
(101, '2013-03-21', '2014-08-01'),
(102, '2014-01-01', '2014-12-31'),
(103, '2012-01-01', '2012-10-11'),
(103, '2012-10-12', '2014-12-31'),
(104, '2012-01-01', '2012-12-25'),
(105, '2012-01-01', '2014-06-30'),
(105, '2014-07-01', '2014-12-31');
CREATE TABLE #WorkID (WorkID INT)
INSERT INTO #WorkID VALUES (101), (102), (103), (104), (105)
CREATE TABLE #Yrs (Yrs INT)
INSERT INTO #Yrs VALUES (2012), (2013), (2014)
SELECT * 
INTO #ResultSet
FROM #WorkID
CROSS JOIN #Yrs;
WITH CTE AS 
(
SELECT Workid, YEAR (Date_) AS Yr, COUNT(*) AS WorkedDays
FROM #Date AS D
INNER JOIN #temp AS T ON D.Date_ >= T.StartDate AND D.Date_ <= T.EndDate
GROUP BY Workid, YEAR (Date_)
)
SELECT R.WorkID,
Yrs AS Year_, 
CASE WHEN Yr % 4 = 0 AND WorkedDays = 366 THEN 'Occupied'
WHEN Yr % 4 <> 0 AND WorkedDays = 365 THEN 'Occupied'
ELSE 'Not Occupied'
END AS OccupiedStatus
FROM #ResultSet AS R
LEFT JOIN CTE AS C ON C.Workid = R.WorkID AND Yrs = Yr
ORDER BY 2, 1

Answer 4

首先需要确定每个工作 ID 的最小 StartDate 和最大 EndDate：

SELECT   [WorkId]
, MIN([StartDate]) AS [StartDate]
, MAX([EndDate]) AS [EndDate]
FROM     [#temp]
GROUP BY [WorkId]

通过一些日期操作，您需要弄清楚您正在评估的年份的开始和结束日期，如下所示：

DECLARE @Year INT = 2012;
DECLARE @YearBegin DATE;
DECLARE @YearEnd date;
SET @YearBegin = DATEADD(yy, @Year - 1900, 0);
SET @YearEnd = DATEADD(DAY, -1, DATEADD(yy, 1, @YearBegin));
SELECT @YearBegin
, @YearEnd;

然后，我们可以根据这些@YearBegin和@yearEnd评估开始和结束日期，以确定"已满"。

这是假设每个 ID 的数据范围没有间隙：

DECLARE @Year INT = 2012;
DECLARE @YearBegin DATE;
DECLARE @YearEnd DATE;
SET @YearBegin = DATEADD(yy, @Year - 1900, 0);
SET @YearEnd = DATEADD(DAY, -1, DATEADD(yy, 1, @YearBegin));
SELECT [WorkId]
, CASE WHEN [fr].[StartDate] <= @YearBegin
AND [fr].[EndDate] >= @YearEnd THEN 'Occupied'
ELSE 'Not Occupied'
END AS [Status]
FROM   (
SELECT   [WorkId]
, MIN([StartDate]) AS [StartDate]
, MAX([EndDate]) AS [EndDate]
FROM     [#temp]
GROUP BY [WorkId]
) AS [fr];

如果每个 ID 的日期范围和/或日期范围可能存在重叠，下面是一个递归 CTE 示例，用于说明这些情况：

DECLARE @Year INT = 2013
, @TotalDayYear INT;
--For the year we are evalutaing, how many days total in that year
--We'll use for comparison later
SET @TotalDayYear = DATEDIFF(
DAY
, CONCAT(@Year - 1, '-12-31')
, CONCAT(@Year, '-12-31')
);

DECLARE @YearOccupied TABLE
(
[WorkId] INT
, [YearOccupied] INT
, [CountDaysOccupied] INT
);

--Recursive CTE to get list of every day a particular ID occupied based on the date ranges.
WITH [Cte]
AS ( SELECT [WorkId]
, [StartDate]
, [EndDate]
, [StartDate] AS [DateOccupied]
FROM   [#temp]
UNION ALL
SELECT     [a].[WorkId]
, [a].[StartDate]
, [a].[EndDate]
, DATEADD(DAY, 1, [b].[DateOccupied]) AS [DateOccupied]
FROM       [#temp] [a]
INNER JOIN [Cte] [b]
ON [b].[WorkId] = [a].[WorkId]
AND [b].[StartDate] = [a].[StartDate]
AND [b].[EndDate] = [a].[EndDate]
AND [b].[DateOccupied] < [a].[EndDate] )
--We'll insert into a table variable a count by year for each Id
INSERT INTO @YearOccupied (
[WorkId]
, [YearOccupied]
, [CountDaysOccupied]
)
SELECT   [do].[WorkId]
, YEAR([DateOccupied]) AS [YearOccupied]
, COUNT(*) AS [DaysOccupiedCount]
FROM     (
--Use a sub-query here to get a distinct Date Occupied
--To account for overlapping data ranges
SELECT DISTINCT [WorkId]
, [DateOccupied]
FROM   [Cte]
) AS [do]
GROUP BY [do].[WorkId]
, YEAR([DateOccupied])
OPTION ( MAXRECURSION 0 );

SELECT          [a].[WorkId]
, @Year AS [YearOccupied]
--Coaleace as we may have a year an ID didn't occupy at all
--compared to the total days of the year to determine fully occupied or not
, CASE WHEN COALESCE([b].[CountDaysOccupied], 0) = @TotalDayYear THEN
'Occupied'
ELSE 'Not Occupied'
END AS [Status]
FROM            (
--We could have a year we want data for that a particular id didn't occupy.
--We'll build a distinct list of ID and our evalution year for doing a left outer to
--accually see if the year was occupied.
SELECT DISTINCT [WorkId]
, @Year AS [YearOccupied]
FROM   @YearOccupied
) AS [a]
LEFT OUTER JOIN @YearOccupied [b]
ON [b].[WorkId] = [a].[WorkId]
AND [b].[YearOccupied] = @Year;