我使用的iReport 3.0.0和PostgreSQL 9.1。 对于报表，我需要日期范围从日期范围发票过滤器比较，如果过滤器范围覆盖 ， 部分覆盖 ，等更为复杂的是打印为每发票代码，可以有每张发票代码的多个日期范围。
表发票
ID Code StartDate EndDate
1 111 1.5.2012 31.5.2012
2 111 1.7.2012 20.7.2012
3 111 25.7.2012 31.7.2012
4 222 1.4.2012 15.4.2012
5 222 18.4.2012 30.4.2012
例子
过滤器：2012年5月1日。 - 2012年6月5日。
结果，我需要得到的是：
code 111 - partialy covered
code 222 - invoice missing
过滤器：2012年5月1日。 - 2012年5月31日。
code 111 - fully covered
code 222 - invoice missing
过滤器：2012年6月1日。 - 2012年6月30日。
code 111 - invoice missing
code 222 - invoice missing
澄清后评论。
你的任务我的理解：
检查所有提供的个人日期范围（ filter ），无论它们是通过在你的表（套码的组合日期范围涵盖invoice ）。
它可以与普通的SQL来完成，但它不是一个简单的任务 。 该步骤可以是：
供应日期范围为过滤器。
合并的日期范围在invoice每码表。 可导致每个代码的一个或多个范围。
寻找过滤器和合并发票之间的重叠
所属分类：全覆盖/部分覆盖。 可能会导致一个全覆盖，一个或两个部分覆盖范围或没有覆盖。 减少覆盖的最高水平。
显示一个行，并与所得到的覆盖范围（过滤器，代码）的每个组合，在一个合理的排序顺序
特设过滤范围
WITH filter(filter_id, startdate, enddate) AS (
VALUES
(1, '2012-05-01'::date, '2012-06-05'::date) -- list filters here.
,(2, '2012-05-01', '2012-05-31')
,(3, '2012-06-01', '2012-06-30')
)
SELECT * FROM filter;
或者把它们放在一个（临时）表，使用该表来代替。
结合每个码重叠/相邻的日期范围
WITH a AS (
SELECT code, startdate, enddate
,max(enddate) OVER (PARTITION BY code ORDER BY startdate) AS max_end
-- Calculate the cumulative maximum end of the ranges sorted by start
FROM invoice
), b AS (
SELECT *
,CASE WHEN lag(max_end) OVER (PARTITION BY code
ORDER BY startdate) + 2 > startdate
-- Compare to the cumulative maximum end of the last row.
-- Only if there is a gap, start a new group. Therefore the + 2.
THEN 0 ELSE 1 END AS step
FROM a
), c AS (
SELECT code, startdate, enddate, max_end
,sum(step) OVER (PARTITION BY code ORDER BY startdate) AS grp
-- Members of the same date range end up in the same grp
-- If there is a gap, the grp number is incremented one step
FROM b
)
SELECT code, grp
,min(startdate) AS startdate
,max(enddate) AS enddate
FROM c
GROUP BY 1, 2
ORDER BY 1, 2
另一个最终SELECT（可能会更快与否，你必须进行测试）：
SELECT DISTINCT code, grp
,first_value(startdate) OVER w AS startdate
,last_value(enddate) OVER w AS enddate
FROM c
WINDOW W AS (PARTITION BY code, grp ORDER BY startdate
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY 1, 2;
结合到一个查询
WITH
-- supply one or more filter values
filter(filter_id, startdate, enddate) AS (
VALUES
(1, '2012-05-01'::date, '2012-06-05'::date) -- cast values in first row
,(2, '2012-05-01', '2012-05-31')
,(3, '2012-06-01', '2012-06-30')
)
-- combine date ranges per code
,a AS (
SELECT code, startdate, enddate
,max(enddate) OVER (PARTITION BY code ORDER BY startdate) AS max_end
FROM invoice
), b AS (
SELECT *
,CASE WHEN (lag(max_end) OVER (PARTITION BY code ORDER BY startdate)
+ 2) > startdate THEN 0 ELSE 1 END AS step
FROM a
), c AS (
SELECT code, startdate, enddate, max_end
,sum(step) OVER (PARTITION BY code ORDER BY startdate) AS grp
FROM b
), i AS ( -- substitutes original invoice table
SELECT code, grp
,min(startdate) AS startdate
,max(enddate) AS enddate
FROM c
GROUP BY 1, 2
)
-- match filters
, x AS (
SELECT f.filter_id, i.code
,bool_or(f.startdate >= i.startdate
AND f.enddate <= i.enddate) AS full_cover
FROM filter f
JOIN i ON i.enddate >= f.startdate
AND i.startdate <= f.enddate -- only overlapping
GROUP BY 1,2
)
SELECT f.*, i.code
,CASE x.full_cover
WHEN TRUE THEN 'fully covered'
WHEN FALSE THEN 'partially covered'
ELSE 'invoice missing'
END AS covered
FROM (SELECT DISTINCT code FROM i) i
CROSS JOIN filter f -- all combinations of filter and code
LEFT JOIN x USING (filter_id, code) -- join in overlapping
ORDER BY filter_id, code;
测试和对我的作品上的PostgreSQL 9.1。
