日期范围之间的字段值 [英] field value between date range

问题描述

请，有人可以帮助我，如何获取文档状态信息...
通常，我需要给定的时间段（开始日期，结束日期过滤器）
来检查文档是否活动（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;

这篇关于日期范围之间的字段值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！