限制报告不显示关闭产品但仍然打开另一个产品的客户？

Question

我需要制作一份报告，在某段时间内将返回有效和无效的客户端。

这是基于一些产品具有开始和结束日期。关闭产品但仍然有另一个打开的客户不需要显示。我的问题是，我不知道如何限制报告才能做到这一点。我试图添加not exists条款，如：

WITH my_with_as AS 
( 
     SELECT p.product_id, 
       p.client_id, 
       p.opening_date, 
       p.ending_date 
     FROM products p 
     WHERE p.opening_date BETWEEN report_start_date AND report_end_date 
     OR  p.ending_date BETWEEN report_open_date AND report_end_date) 
SELECT cl.d_start, 
     CASE 
       WHEN cl.d_stop BETWEEN report_start_date AND report_end_date THEN my_with_as.ending_date 
       ELSE NULL 
     END 
FROM ( 
       SELECT 
        ( 
          SELECT Min(d1.opening_date) 
          FROM my_with_as d1 
          WHERE d1.client_id=c.client_id) d_start, 
        ( 
          SELECT Max(d2.ending_date) 
          FROM my_with_as d2 
          WHERE d2.client_id=c.client_id) d_stop, 
        c.* 
       FROM clients c) cl, 
     my_with_as 
WHERE cl.client_id=my_with_as.client_id 
AND NOT EXISTS 
     ( 
       SELECT p.product_id 
       FROM products p 
       WHERE my_with_as.client_id = p.client_id 
       AND p.product_id<>my_with_as.product_id 
       AND Nvl(p.ending_date,report_end_date+1)>report_end_date 
       AND p.start_date < my_with_as.ending_date 

其中my_with_as是一款具有与所有打开的或在报告的时间段关闭了产品查询。

问题是为01.05.2014 - 04.04.2015一个报告期，有一个客户端：

-product_1: opened on 04.04.2001, closed on 25.07.2014 

-product_2: opened on 04.04.2010, closed on 25.03.2015 

-product_3: opened on 01.01.2015, closed on 04.04.2015 

我的报告显示，无论是1号产品和第三届一个即使它不应该显示任何东西。有没有办法来验证产品的间隔是否重叠？任何暗示或帮助都非常感谢，因为这已经让我坚持了3天。

---

----编辑（从评论复制表定义的问题）-----

产品表：product_id, client_id, opening_date, ending_date.
客户表中有刚的CLIENT_ID - 我将其简化为测试目的。
报告将有2个日期作为参数：start_report，end_report -

Answer 1

我相信这是你要找的查询，或者至少这将是对您有用：

with report as (select date '2014-05-01' r1, date '2015-04-04' r2 from dual), 
mwa AS (
    SELECT p.product_id, p.client_id, p.opening_date, p.ending_date, r1, r2 
    FROM products p cross join report r 
    WHERE p.opening_date BETWEEN r1 AND r2 OR p.ending_date BETWEEN r1 AND r2) 
SELECT cl.d_start, CASE WHEN cl.d_stop BETWEEN r1 AND r2 THEN mwa.ending_date END d_stop 
    FROM 
    (SELECT 
     (SELECT MIN(opening_date) FROM mwa WHERE client_id = c.client_id) d_start, 
     (SELECT MAX(ending_date) FROM mwa WHERE client_id = c.client_id) d_stop, c.* 
     FROM clients c) cl join mwa on mwa.client_id = cl.client_id 
    WHERE NOT EXISTS (
     SELECT p.product_id from products p 
     WHERE mwa.client_id = p.client_id AND p.product_id<>mwa.product_id 
      and not (p.opening_date BETWEEN r1 AND r2 
      OR p.ending_date between r1 and r2)) 

它没有返回给定示例的行，因为一行不属于分析期间 - 所以我们不需要为此客户端的其余行。 请替换第一行中的日期以更改周期，但使用符号'yyyy-mm-dd'，或在此处使用to_date()。

SQLFiddle

在SQLFiddle我加入一个行其他客户端测试解决方案，所以我们有一个返回行。

我怀疑这个查询可能会被简化，但我不想太干扰你的代码。

---

编辑 - 根据评论：

with report as (select date '2015-02-01' r1, date '2015-04-03' r2 from dual), 
p1 as (select product_id pid, client_id cid, opening_date d1, ending_date d2, r1, r2 
    from products p cross join report r), 
p2 as (select pid, cid, d1, d2, 
    case when (d1 < r2 and (d2 is null or d2 > r1)) then 1 else 0 end overlap, 
    case when (d1 < r2 and d2 is not null and d2 < r2) then 1 else 0 end closed 
    from p1) 
select * from p2 order by cid, pid 

PID    CID D1   D2   OVERLAP  CLOSED 
---------- ---------- ---------- ---------- ---------- ---------- 
product_1   1 2014-04-04 2014-07-25   0   1 
product_2   1 2015-01-04 2015-03-25   1   1 
product_3   1 2015-01-01 2015-04-01   1   1 
product_1   2 2015-01-01 2015-04-04   1   0 
product_1   3 2015-04-04 2015-07-25   0   0 
product_2   3 2015-01-01 2015-04-04   1   0 

overlap = 1 means that product was "active" in report period, probably these are the 
      only rows which interests you 
closed = 1 means that product has closed status at the end of period 

上面的查询为我们提供了每个产品的扩展信息。现在，您只能过滤overlap = 1的行 并使用它们。下一步你可以做什么的例子是：

with report as (select date '2015-02-01' r1, date '2015-07-20' r2 from dual), 
p1 as (select product_id pid, client_id cid, opening_date d1, ending_date d2, r1, r2 
    from products p cross join report r), 
p as (select pid, cid, d1, d2, r2, 
    case when (d1 < r2 and (d2 is null or d2 > r1)) then 1 else 0 end overlap, 
    case when (d1 < r2 and d2 is not null and d2 < r2) then 1 else 0 end closed 
    from p1) 
select distinct c.name, d1, nvl(d2, r2) d2 
    from p join clients c on c.client_id = p.cid 
    where overlap = 1 and not exists (
    select 1 from p tmp where overlap = 1 and closed = 1 
     and cid = p.cid and pid <> p.pid) 

NAME  D1   D2  
---------- ---------- ---------- 
Jones  2015-01-01 2015-04-04 
Smith  2015-01-01 2015-04-04