SQL日期范围查询 - 表格比较

Question

SQL日期范围查询 - 表格比较

5

我有两个SQL Server表格，包含以下信息：

表格 t_venues:

venue_id 是唯一的。

venue_id  |  start_date  |  end_date
       1  |  01/01/2014  |  02/01/2014
       2  |  05/01/2014  |  05/01/2014
       3  |  09/01/2014  |  15/01/2014
       4  |  20/01/2014  |  30/01/2014

Table t_venueuser:

venue_id is not unique

venue_id  |  start_date  |  end_date
       1  |  02/01/2014  |  02/01/2014
       2  |  05/01/2014  |  05/01/2014
       3  |  09/01/2014  |  10/01/2014
       4  |  23/01/2014  |  25/01/2014

我需要从这两个表中找到每个范围没有选择的日期，因此输出应该像这样：

venue_id  |  start_date  |  end_date
       1  |  01/01/2014  |  01/01/2014
       3  |  11/01/2014  |  15/01/2014
       4  |  20/01/2014  |  22/01/2014
       4  |  26/01/2014  |  30/01/2014

我可以使用 'except' 比较两个表并获取来自 t_venues 的日期范围，但我无法让查询产生未被选择的日期。任何帮助将不胜感激。

- samhankin

结果中的场馆3行不应该是11/01/2014至15/01/2014吗？ - SWeko

是的，谢谢 - 发现得好！ - samhankin

1

如果有帮助，请不要忘记将答案标记为已接受。http://www.stackoverflow.com/help/someone-answers - Mack

2个回答

1

这是一个完全的hack，但它能给你想要的结果。我只在你提供的数据上测试过，所以在更大的数据集上可能会有问题。

一般来说，你正在解决的是间隔和岛屿问题的变体，简而言之，这是一个序列，其中某些项缺失。缺失的项被称为间隙，存在的项被称为岛屿。如果你想了解这个问题，请查看以下文章：

- Simple talk article - blogs.MSDN article - SO answers tagged gaps-and-islands 代码：

;with dates as
(
    SELECT  vdates.venue_id,    
            vdates.vdate
    FROM  ( SELECT DATEADD(d,sv.number,v.start_date) vdate
                 , v.venue_id
            FROM t_venues v
            INNER JOIN master..spt_values sv 
                ON sv.type='P'
               AND sv.number BETWEEN 0 AND datediff(d, v.start_date, v.end_date)) vdates
    LEFT JOIN t_venueuser vu
        ON vdates.vdate >= vu.start_date
       AND vdates.vdate <= vu.end_date
       AND vdates.venue_id = vu.venue_id
    WHERE ISNULL(vu.venue_id,-1) = -1
)
SELECT venue_id, ISNULL([1],[2]) StartDate, [2] EndDate
FROM   (SELECT venue_id, rDate, ROW_NUMBER() OVER (PARTITION BY venue_id, DateType ORDER BY rDate) AS rType, DateType as dType
        FROM(   SELECT d1.venue_id
                      ,d1.vdate AS rDate
                      ,'1' AS DateType
                FROM dates AS d1    
                LEFT JOIN dates AS d0
                    ON DATEADD(d,-1,d1.vdate) = d0.vdate
                LEFT JOIN dates AS d2       
                    ON DATEADD(d,1,d1.vdate) = d2.vdate
                WHERE CASE ISNULL(d2.vdate, '01 Jan 1753') WHEN '01 Jan 1753' THEN '2' ELSE '1' END = 1
                AND ISNULL(d0.vdate, '01 Jan 1753') = '01 Jan 1753'
                UNION 
                SELECT d1.venue_id
                      ,ISNULL(d2.vdate,d1.vdate)
                      ,'2'
                FROM dates AS d1    
                LEFT JOIN dates AS d2       
                    ON DATEADD(d,1,d1.vdate) = d2.vdate
                WHERE CASE ISNULL(d2.vdate, '01 Jan 1753') WHEN '01 Jan 1753' THEN '2' ELSE '1' END = 2
            ) res
        ) src
PIVOT   (MIN (rDate)
        FOR dType IN
        ( [1], [2] )
        ) AS pvt

Results:

venue_id    StartDate   EndDate
1           2014-01-01  2014-01-01
3           2014-01-11  2014-01-15
4           2014-01-20  2014-01-22
4           2014-01-26  2014-01-30

- Mack

这真是太棒了。谢谢！我已经在一个更大的数据集上运行它，唯一的问题是当事件已经过去并且在事件开始时有可用日期时，它只显示1天？例如：场馆是2014年01月09日至2014年10月09日，场馆用户预订时间为2014年05月09日至2014年10月09日，输出将显示2014年04月09日至2014年04月09日？我现在正在尝试解决它！ - samhankin

@samhankin，很高兴我们能够帮到你，如果还有其他需要帮助的地方，请告诉我们。 - Mack

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gvee · Accepted Answer

日历表！

又一个完美的日历表候选者。如果您不想去搜索日历表，这里有我之前制作的一个。

设置数据

DECLARE @t_venues table (
   venue_id   int
 , start_date date
 , end_date   date
);

INSERT INTO @t_venues (venue_id, start_date, end_date)
  VALUES (1, '2014-01-01', '2014-01-02')
       , (2, '2014-01-05', '2014-01-05')
       , (3, '2014-01-09', '2014-01-15')
       , (4, '2014-01-20', '2014-01-30')
;

DECLARE @t_venueuser table (
   venue_id   int
 , start_date date
 , end_date   date
);

INSERT INTO @t_venueuser (venue_id, start_date, end_date)
  VALUES (1, '2014-01-02', '2014-01-02')
       , (2, '2014-01-05', '2014-01-05')
       , (3, '2014-01-09', '2014-01-10')
       , (4, '2014-01-23', '2014-01-25')
;

查询

SELECT t_venues.venue_id
     , calendar.the_date
     , CASE WHEN t_venueuser.venue_id IS NULL THEN 1 ELSE 0 END As is_available
FROM   dbo.calendar /* see: http://gvee.co.uk/files/sql/dbo.numbers%20&%20dbo.calendar.sql for an example */
 INNER
  JOIN @t_venues As t_venues
    ON t_venues.start_date <= calendar.the_date
   AND t_venues.end_date   >= calendar.the_date
 LEFT
  JOIN @t_venueuser As t_venueuser
    ON t_venueuser.venue_id = t_venues.venue_id
   AND t_venueuser.start_date <= calendar.the_date
   AND t_venueuser.end_date   >= calendar.the_date
ORDER
    BY t_venues.venue_id
     , calendar.the_date
;

结果

venue_id    the_date                is_available
----------- ----------------------- ------------
1           2014-01-01 00:00:00.000 1
1           2014-01-02 00:00:00.000 0
2           2014-01-05 00:00:00.000 0
3           2014-01-09 00:00:00.000 0
3           2014-01-10 00:00:00.000 0
3           2014-01-11 00:00:00.000 1
3           2014-01-12 00:00:00.000 1
3           2014-01-13 00:00:00.000 1
3           2014-01-14 00:00:00.000 1
3           2014-01-15 00:00:00.000 1
4           2014-01-20 00:00:00.000 1
4           2014-01-21 00:00:00.000 1
4           2014-01-22 00:00:00.000 1
4           2014-01-23 00:00:00.000 0
4           2014-01-24 00:00:00.000 0
4           2014-01-25 00:00:00.000 0
4           2014-01-26 00:00:00.000 1
4           2014-01-27 00:00:00.000 1
4           2014-01-28 00:00:00.000 1
4           2014-01-29 00:00:00.000 1
4           2014-01-30 00:00:00.000 1

(21 row(s) affected)

说明

我们的日历表包含每个日期的条目。

我们将我们的 t_venues（顺便说一句，如果可以选择，请删除前缀t_！）与我们的start_date和end_date之间的每一天进行连接。以下是仅针对venue_id=4的示例输出：

venue_id    the_date
----------- -----------------------
4           2014-01-20 00:00:00.000
4           2014-01-21 00:00:00.000
4           2014-01-22 00:00:00.000
4           2014-01-23 00:00:00.000
4           2014-01-24 00:00:00.000
4           2014-01-25 00:00:00.000
4           2014-01-26 00:00:00.000
4           2014-01-27 00:00:00.000
4           2014-01-28 00:00:00.000
4           2014-01-29 00:00:00.000
4           2014-01-30 00:00:00.000

(11 row(s) affected)

现在我们每天都有一行数据，我们与 t_venueuser 表进行外连接。我们以与之前类似的方式连接它，但增加了一个细节：我们需要基于 venue_id 进行连接！

对于 venue_id=4，运行结果如下：

venue_id    the_date                t_venueuser_venue_id
----------- ----------------------- --------------------
4           2014-01-20 00:00:00.000 NULL
4           2014-01-21 00:00:00.000 NULL
4           2014-01-22 00:00:00.000 NULL
4           2014-01-23 00:00:00.000 4
4           2014-01-24 00:00:00.000 4
4           2014-01-25 00:00:00.000 4
4           2014-01-26 00:00:00.000 NULL
4           2014-01-27 00:00:00.000 NULL
4           2014-01-28 00:00:00.000 NULL
4           2014-01-29 00:00:00.000 NULL
4           2014-01-30 00:00:00.000 NULL

(11 row(s) affected)

看看我们在没有 t_venueuser 记录的行中有一个 NULL 值。天才，不是吗？;-) 因此，在我的第一个查询中，我给出了一个快速的 CASE 语句，显示可用性（1=可用，0=不可用）。这仅供说明，但对您可能有用。

然后，您可以将查询包装起来，然后在计算列上应用额外的过滤器，或者只需添加一个 where 子句：WHERE t_venueuser.venue_id IS NULL，就可以达到同样的效果。