给定SQL中的日期范围，查找第一个可用日期 [英] Find first available date, given a date range in SQL

问题描述

假设您有一个表，其中包含日期范围，而这些日期不可用.

Suppose you have a table with date ranges for which those dates are NOT available.

+------------+------------+
| StartDate  | EndDate    |
+------------+------------+
| 2014-10-1  | 2014-10-15 |
+------------+------------+
| 2014-11-4  | 2014-11-28 |
+------------+------------+
| 2014-12-17 | NULL       |
+------------+------------+

EndDate为NULL表示直到时间结束.因此，12/17之后没有可用的日期.

An EndDate of NULL means till the end of time. So there are no available dates after 12/17.

鉴于日期范围，我需要找到第一个可用日期.我不太擅长SQL，也想不到如何做到这一点.

Given a date range, I need to find the first available date. I'm not that great at SQL and I can't think of how this could be done.

示例:给定所需的日期范围

Examples: Given the desired date range

2014-10-13至2014-11-17，查询应返回2014-10-16

2014-10-13 to 2014-11-17, the query should return 2014-10-16

2014-10-13至2014-11-30，查询应返回2014-10-16

2014-10-13 to 2014-11-30, the query should return 2014-10-16

2014-10-21至2014-11-30，查询应返回2014-10-21

2014-10-21 to 2014-11-30, the query should return 2014-10-21

2014-12-01至2015-1-13，查询应返回2014-12-01

2014-12-01 to 2015-1-13, the query should return 2014-12-01

我将不胜感激.谢谢.

推荐答案

尝试一下，没有循环！(顺便说一句，我相信您的最后一个测试用例是是错误的)

Try this, no loops! (BTW your last test case is was wrong I believe)

CREATE TABLE [dbo].[Dates]([StartDate] [datetime] NULL, [EndDate] [datetime] NULL ) ON [PRIMARY]

INSERT INTO [dbo].[Dates]([StartDate], [EndDate])
SELECT '20141001 00:00:00.000', '20141015 00:00:00.000' UNION ALL
SELECT '20141104 00:00:00.000', '20141128 00:00:00.000' UNION ALL
SELECT '20141217 00:00:00.000', NULL

DECLARE @Date1 DATETIME;
DECLARE @Date2 DATETIME;

-- Test case 1
SET @Date1 = '2014-10-13';
SET @Date2 = '2014-11-17';

-- Test case 2
SET @Date1 = '2014-10-13';
SET @Date2 = '2014-11-30';

-- Test case 3
SET @Date1 = '2014-10-21';
SET @Date2 = '2014-11-30';

-- Test case 4
SET @Date1 = '2014-12-01';
SET @Date2 = '2015-01-13';

-- Generate a temp table of dates from @Date1 to @Date2
SELECT TOP (DATEDIFF(DAY, @Date1, @Date2)+1)
  [date] = DATEADD(day, ROW_NUMBER() OVER (ORDER BY [object_id]) - 1, @Date1)
INTO
  #DateRange
FROM
  sys.all_objects

-- Remove non-available dates
DELETE #DateRange  
WHERE  
  EXISTS(SELECT 1 FROM dbo.Dates WHERE date BETWEEN StartDate AND ISNULL(EndDate, date))

-- Select first available date  
SELECT TOP 1 * FROM #DateRange AS dr ORDER BY date

这篇关于给定SQL中的日期范围，查找第一个可用日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！