日期范围之间的字段值

本文介绍了日期范围之间的字段值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

请，有人可以帮助我，如何获取文档状态信息... 通常，我需要给定的时间段（开始日期，结束日期过滤器）来检查文档是否活动（A）或非活动（I）

Please, can someone help me, how to get information of doc status... Generally, I need for given period of time (start date, end date filters) to check if document is Active (A) or Inactive (I)

Table Documents ID Doc Date Status 1 11 1.1.2012. A 2 11 1.4.2012. I 3 11 25.4.2012. A 4 11 1.6.2012. I 5 22 18.4.2012. A 6 22 30.4.2012. I Dynamic filters: @start,@end Example: @start= 2.3.2012 @end=5.5.2012 Result should be 11 2.3.-1.4. Status=A 1.4.-25.4 Status=I 25.4.-5.5. Status=A 22 2.3.-18.4. 'not exist' 18.4-30.4. Status=A 30.4.-5.5. Status=I If filter is @start= 1.2. @end= 28.2. Result should be 11 'A' 22 'not exist' If filter is @start= 18.4. @end= 20.4. Result should be 11 'I' 22 'A'

编辑：

对不起，我不想听起来像是为我做 ... 我尝试过这样的事情

Sorry, I didn't want to sound like 'do it for me'... I have tried something like this

WITH a AS ( SELECT documents.*,lag(date) OVER (PARTITION BY doc ORDER BY DATE) AS pre_date FROM documents ORDER BY DATE ) SELECT a.* from a WHERE (@start between a.pre_date AND a.date) AND (@end between a.pre_date AND a.date)

这不是我所需要的。 这也是sql小提琴 sqlfiddlelink 中的示例。 我更改过滤器表以测试@start和@end的不同值

It is not quite what I need. Here is also example in sql fiddle sqlfiddlelink. I change Filter table to test for different values of @start and @end

谢谢

推荐答案

基本上，@ Glenn的答案涵盖了这一点。我赞成。我仅将其发布以演示其他详细信息-太多内容无法放入评论中：

Basically, @Glenn's answer covers it. I upvoted it. I only post this to demonstrate additional details - too many to fit into a comment:

• 使用多行 INSERT 语法。

在 CTE ，这比为此创建一个额外的表要方便得多。

Provide filters in a CTE, that's much more convenient than creating an extra table for that.

此查询可以一次处理多个过滤器。

This query can handle multiple filters at once.

使用 lead（date，1，'infinity'） 来消除 COALESCE 的需要。

演示一种不太复杂的输入日期文字的方法-ISO 8601格式'yyyy-mm-dd'与 any 语言环境是明确的：

Demonstrate a less convoluted way to enter date literals - The ISO 8601 format 'yyyy-mm-dd' is unambiguous with any locale:

'2012-02-03'::date

或

date '2012-02-03'

而不是：

to_date('2012-02-03', 'yyyy-mm-dd')

将所有内容以一种噪音较小，易读的格式

Put it all in a less noisy, more readable format

CREATE TEMP TABLE documents (id int, doc int, date date, status "char"); INSERT INTO documents VALUES (1,'11','2012-01-01','A') ,(2,'11','2012-04-01','I') ,(3,'11','2012-04-25','A') ,(4,'11','2012-06-01','I') ,(5,'22','2012-04-18','A') ,(6,'22','2012-04-30','I'); WITH filter(filter_id, start_date, end_date) AS( VALUES (1, '2012-04-18'::date, '2012-04-20'::date) ,(2, '2012-03-02'::date, '2012-05-05'::date) ) , d AS ( SELECT doc, status, date AS d1 ,lead(date,1,'infinity') OVER (PARTITION BY doc ORDER BY date) AS d2 FROM documents ) SELECT f.filter_id, d.doc ,GREATEST(f.start_date, d.d1) AS start ,LEAST(f.end_date, d.d2) AS end ,d.status FROM filter f, d WHERE f.start_date <= d.d2 AND f.end_date >= d.d1 ORDER BY f.filter_id, d.doc, d.d1;