oracle订阅服务器,Oracle SQL Developer订阅服务器-创建交叉表

cross apply

select uh.subscriber_id, m.month,

case when trunc(uh.start_date, 'MM') <= m.month

and (uh.end_date is null or uh.end_date >= add_months(m.month, 1))

then 1 else 0 end as active

from upcall_history uh

cross apply (

select add_months(trunc(sysdate, 'MM'), - level) as month

from dual

connect by level <= 36

) m

order by uh.subscriber_id, m.month;

with upcall_history (subscriber_id, start_date, end_date) as (

select 1, date '2015-09-04', '2015-12-15' from dual

union all select 2, date '2017-12-04', '2018-05-15' from dual

)

SUBSCRIBER_ID MONTH ACTIVE

------------- ---------- ----------

1 2015-07-01 0

1 2015-08-01 0

1 2015-09-01 1

1 2015-10-01 1

1 2015-11-01 1

1 2015-12-01 0

1 2016-01-01 0

...

2 2017-11-01 0

2 2017-12-01 1

2 2018-01-01 1

2 2018-02-01 1

2 2018-03-01 1

2 2018-04-01 1

2 2018-05-01 0

2 2018-06-01 0

with m (month) as (

select add_months(trunc(sysdate, 'MM'), - level)

from dual

connect by level <= 36

),

i (subscriber_id) as (

select distinct subscriber_id

from upcall_history

)

select i.subscriber_id, m.month,

case when uh.subscriber_id is null then 0 else 1 end as active

from m

cross join i

left join upcall_history uh

on uh.subscriber_id = i.subscriber_id

and trunc(uh.start_date, 'MM') <= m.month

and (uh.end_date is null or uh.end_date >= add_months(m.month, 1))

order by i.subscriber_id, m.month;