根据日期范围创建多行

Question

下面有一个日历查询和一个表格。我有一个成员的StartDate和结束日期。在我的日历表上，我还捕获了一个基于startDate的“每周”。我想捕捉一下，如果一个成员在那个星期的任何时候都是活跃的。见预期结果。

SELECT DISTINCT
  --CA.CALENDAR_DATE,
      TO_CHAR(CALENDAR_DATE,'MM/DD/YYYY') AS CALENDAR_DATE                                
           TO_CHAR(NEXT_DAY(CALENDAR_DATE, 'Monday') - 7, 'MM/DD/YY-') || 
      TO_CHAR(NEXT_DAY(CALENDAR_DATE, 'Monday') - 1, 'MM/DD/YY') AS WEEK_OF_YEAR,

      ROW_NUMBER () OVER ( ORDER BY CALENDAR_DATE) AS MasterCalendar_RNK

     FROM CALENDAR CA
     WHERE 1=1  
       --AND CA.CALENDAR_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE), -12) AND TRUNC(SYSDATE)
       --AND CA.CALENDAR_DATE BETWEEN TRUNC(SYSDATE) -5 AND TRUNC(SYSDATE)
       ORDER BY TO_DATE(CALENDAR_DATE,'MM/DD/YYYY') DESC

表格

Member    StartDate    EndDate    
  A          1/31/17      
  B          2/1/17      2/15/17

预期产出：

Member    StartDate    EndDate    Week_Of_Year        Active
  A         1/31/17                  1/30/17-2/5/17      1
  A         1/31/17                  2/6/17-2/12/17      1
  A         1/31/17                  2/13/17-2/19/17     1
  B         2/1/17      2/15/17      1/30/17/2/5/17      1
  B         2/1/17      2/15/17      2/6/17-2/12/17      1
  B         2/1/17      2/15/17      2/13/17-2/19/17     1 

当前查询：

WITH MASTER_CALENDAR AS (
SELECT TRUNC(SYSDATE) + 1 - LEVEL , A.CALENDAR_DATE
FROM (SELECT C.CALENDAR_DATE FROM MST.CALENDAR C WHERE 1=1 AND C.CALENDAR_DATE > SYSDATE-30 AND C.CALENDAR_DATE < SYSDATE) A

WHERE 1=1

CONNECT BY LEVEL <= 1 --NEED TO UPDATE?

ORDER BY A.CALENDAR_DATE  DESC       
                         ),

ActiveMembers AS (
SELECT H.CLT_CLT_PGMID, H.START_DT

  ,CASE WHEN TRUNC(H.END_DT) = '1-JAN-3000' 
  THEN SYSDATE 
  ELSE TO_DATE(H.END_DT) 
  END AS END_DT

 FROM H
 WHERE 1=1 
  AND H.CLT_CLT_PGMID IN ('1','2','3')
                        )

SELECT CLT_CLT_PGMID, STARTDATE, ENDDATE, WEEK_OF_YEAR, ACTIVE -- but not week_start
FROM (
SELECT DISTINCT A.CLT_CLT_PGMID,
  TO_CHAR(A.START_DT, 'MM/DD/YY') AS STARTDATE,
  TO_CHAR(A.END_DT, 'MM/DD/YY') AS ENDDATE,
  NEXT_DAY(CAL.CALENDAR_DATE, 'Monday') - 7 AS WEEK_START, -- for ordering later
  TO_CHAR(NEXT_DAY(CAL.CALENDAR_DATE, 'Monday') - 7, 'MM/DD/YY-') || 
    TO_CHAR(NEXT_DAY(CAL.CALENDAR_DATE, 'Monday') - 1, 'MM/DD/YY') AS WEEK_OF_YEAR,
  1 AS ACTIVE

FROM ActiveMembers A
  INNER JOIN MASTER_CALENDAR CAL ON CAL.CALENDAR_DATE BETWEEN A.START_DT AND A.END_DT
                                                                     --BETWEEN TO_CHAR(A.START_DT,'MM/DD/YYYY') AND COALESCE(A.END_DT,(SYSDATE))                                                                  
                                      ) 
WHERE 1=1

ORDER BY 
CLT_CLT_PGMID , STARTDATE, ENDDATE, WEEK_START
                    ;

Answer 1

由于日历查询当前生成字符串，因此返回日历表、将其连接到成员/日期表并重新生成周范围字符串将更加简单：

使用CTE来表示您的日历表(目前只显示最近几周的日期)和成员数据：

with calendar(calendar_date) as (
  select trunc(sysdate) + 1 - level from dual connect by level <= 42
),
mytable (member, startdate, enddate) as (
  select cast('A' as varchar2(6)), date '2017-01-31', cast (null as date) from dual
  union all select cast('B' as varchar2(6)), date '2017-02-01', date '2017-02-15' from dual
)
select member, startdate, enddate, week_of_year, active -- but not week_start
from (
    select distinct m.member,
      to_char(m.startdate, 'MM/DD/YY') as startdate,
      to_char(m.enddate, 'MM/DD/YY') as enddate,
      next_day(c.calendar_date, 'Monday') - 7 as week_start, -- for ordering later
      to_char(next_day(c.calendar_date, 'Monday') - 7, 'MM/DD/YY-') || 
        to_char(next_day(c.calendar_date, 'Monday') - 1, 'MM/DD/YY') as week_of_year,
      1 as active
    from mytable m
    join calendar c
    on c.calendar_date between m.startdate and coalesce(m.enddate, trunc(sysdate))
)
order by member, startdate, enddate, week_start;

获取

MEMBER STARTDAT ENDDATE  WEEK_OF_YEAR          ACTIVE
------ -------- -------- ----------------- ----------
A      01/31/17          01/30/17-02/05/17          1
A      01/31/17          02/06/17-02/12/17          1
A      01/31/17          02/13/17-02/19/17          1
A      01/31/17          02/20/17-02/26/17          1
B      02/01/17 02/15/17 01/30/17-02/05/17          1
B      02/01/17 02/15/17 02/06/17-02/12/17          1
B      02/01/17 02/15/17 02/13/17-02/19/17          1

您还没有为没有结束日期的成员指定上限，所以我今天通过coalesce()使用了。

内部查询仅用于排序，因为不能使用week范围字符串，而且您不希望看到该周单独开始；您也不能根据未选择的字段使用distinct和order。

Answer 2

我会以和亚历克斯相似的方式做这件事，但略有不同。考虑到您的周从星期一开始，我将使用TRUNC(dt, 'iw')获得指定日期的一周的ISO开始(碰巧定义为星期一)。然后，在加入您的桌子之前，我将得到这些值的不同值，如下所示：

with calendar as (select trunc(sysdate) - level + 1 calendar_date
                  from   dual
                  connect by level <= 50),
   your_table as (select 'A' member, date '2017-01-31' startdate, NULL enddate from dual union all
                  select 'B' member, date '2017-02-01' startdate, date '2017-02-15' enddate from dual)
select yt.member,
       yt.startdate,
       yt.enddate,
       to_char(c.week_start, 'mm/dd/yyyy')
         || ' - ' || to_char(c.week_start + 6, 'mm/dd/yyyy') week_of_year,
       1 as active
from   your_table yt
       inner join (select distinct trunc(cl.calendar_date, 'iw') week_start
                   from   calendar cl) c on c.week_start <= nvl(yt.enddate, SYSDATE) AND c.week_start + 6 >= yt.startdate
order by yt.member,
         c.week_start;
MEMBER STARTDATE  ENDDATE    WEEK_OF_YEAR                ACTIVE
------ ---------- ---------- ----------------------- ----------
A      01/31/2017            01/30/2017 - 02/05/2017          1
A      01/31/2017            02/06/2017 - 02/12/2017          1
A      01/31/2017            02/13/2017 - 02/19/2017          1
A      01/31/2017            02/20/2017 - 02/26/2017          1
B      02/01/2017 02/15/2017 01/30/2017 - 02/05/2017          1
B      02/01/2017 02/15/2017 02/06/2017 - 02/12/2017          1
B      02/01/2017 02/15/2017 02/13/2017 - 02/19/2017          1

和Alex一样，我假设您的空结束日期一直持续到今天(sysdate)。但是，看看B成员的结果，看起来你在寻找一个重叠的范围(因为1月30日不是在2月1日到15日之间)，所以我已经相应地修改了我的联接条款。这会为成员A带来额外的行，所以您可能希望在sysdate的前一个星期日之前运行null结束日期吗？不确定。如果你需要的话，我相信你自己也能修改的。