field value between date range

Question

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'

EDIT:

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)

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

Thanks

Answer 1

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

• Use the multi-row INSERT syntax.

• 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.

• Use lead(date,1,'infinity') to obviate the need for COALESCE.

• 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
  

  or

  date '2012-02-03'
  

  instead of:

  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;

Answer 2

This query seems to generate what you are looking for, using the "filter" table you have defined on sqlfiddle. It doesn't include "not exist" rows. I'm not sure if you really wanted that or if you just wanted to show that it doesn't exist. I assume the latter. Otherwise some additional periods from the filter table will need to be "unioned" in I guess.

The idea is to first create periods like you were trying to do with "lag", but use "lead" to say the end of this period is the start of the next period. Probably want to subtract 1 day from the lead to make the end date non-inclusive, but I didn't want to convolute this more.

• If there is no end period, use the filter end period (coalesce)
• Any start dates less then the filter start date is bumped up to the filter start date (greatest)
• Any end dates greater than the filter end date are reduced to the filter end date (least)

Query:

SELECT id, doc, status, from_date, to_date
  FROM ( SELECT id, doc, status, GREATEST(d.date, f.start_date) AS from_date
               ,LEAST( COALESCE( lead(date) OVER (PARTITION BY doc ORDER BY date)
                                ,f.end_date
                               )
                       ,f.end_date ) AS to_date
           FROM documents d
               ,filter f
        ) d
  WHERE from_date < to_date
  ORDER BY doc, from_date;

Setup:

CREATE TABLE documents(id int, doc int, date date, status varchar (1));

insert into documents values(1, 11, to_date('2012-01-01', 'yyyy-mm-dd'),'A');
insert into documents values(2, 11, to_date('2012-04-01', 'yyyy-mm-dd'),'I');
insert into documents values(3, 11, to_date('2012-04-25', 'yyyy-mm-dd'),'A');
insert into documents values(4, 11, to_date('2012-06-01', 'yyyy-mm-dd'),'I');
insert into documents values(5, 22, to_date('2012-04-18', 'yyyy-mm-dd'),'A');
insert into documents values(6, 22, to_date('2012-04-30', 'yyyy-mm-dd'),'I');

CREATE TABLE filter(start_date date, end_date date);

Run:

postgres=#     insert into filter values(to_date('2012-02-03', 'yyyy-mm-dd'), to_date('2012-05-05', 'yyyy-mm-dd'));
INSERT 0 1
postgres=#     SELECT id, doc, status, from_date, to_date
postgres-#       FROM ( SELECT id, doc, status, GREATEST(d.date, f.start_date) AS from_date
postgres(#                    ,LEAST( COALESCE( lead(date) OVER (PARTITION BY doc ORDER BY date)
postgres(#                                     ,f.end_date
postgres(#                                    )
postgres(#                            ,f.end_date ) AS to_date
postgres(#                FROM documents d
postgres(#                    ,filter f
postgres(#             ) d
postgres-#       WHERE from_date < to_date
postgres-#       ORDER BY doc, from_date
postgres-# ;
 id | doc | status | from_date  |  to_date
----+-----+--------+------------+------------
  1 |  11 | A      | 2012-02-03 | 2012-04-01
  2 |  11 | I      | 2012-04-01 | 2012-04-25
  3 |  11 | A      | 2012-04-25 | 2012-05-05
  5 |  22 | A      | 2012-04-18 | 2012-04-30
  6 |  22 | I      | 2012-04-30 | 2012-05-05
(5 rows)


postgres=#     truncate table filter;
TRUNCATE TABLE
postgres=#     insert into filter values(to_date('2012-01-02', 'yyyy-mm-dd'), to_date('2012-02-28', 'yyyy-mm-dd'));
INSERT 0 1
postgres=#     SELECT id, doc, status, from_date, to_date
postgres-#       FROM ( SELECT id, doc, status, GREATEST(d.date, f.start_date) AS from_date
postgres(#                    ,LEAST( COALESCE( lead(date) OVER (PARTITION BY doc ORDER BY date)
postgres(#                                     ,f.end_date
postgres(#                                    )
postgres(#                            ,f.end_date ) AS to_date
postgres(#                FROM documents d
postgres(#                    ,filter f
postgres(#             ) d
postgres-#       WHERE from_date < to_date
postgres-#       ORDER BY doc, from_date;
 id | doc | status | from_date  |  to_date
----+-----+--------+------------+------------
  1 |  11 | A      | 2012-01-02 | 2012-02-28
(1 row)


postgres=#     truncate table filter;
TRUNCATE TABLE
postgres=#     insert into filter values(to_date('2012-04-18', 'yyyy-mm-dd'), to_date('2012-04-20', 'yyyy-mm-dd'));
INSERT 0 1
postgres=#     SELECT id, doc, status, from_date, to_date
postgres-#       FROM ( SELECT id, doc, status, GREATEST(d.date, f.start_date) AS from_date
postgres(#                    ,LEAST( COALESCE( lead(date) OVER (PARTITION BY doc ORDER BY date)
postgres(#                                     ,f.end_date
postgres(#                                    )
postgres(#                            ,f.end_date ) AS to_date
postgres(#                FROM documents d
postgres(#                    ,filter f
postgres(#             ) d
postgres-#       WHERE from_date < to_date
postgres-#       ORDER BY doc, from_date;
 id | doc | status | from_date  |  to_date
----+-----+--------+------------+------------
  2 |  11 | I      | 2012-04-18 | 2012-04-20
  5 |  22 | A      | 2012-04-18 | 2012-04-20
(2 rows)


postgres=#