我有下表：如果需要，我也有日历表。
ID Start_dt End_dt
1 1/9/2016 3/10/2016
预期产量：
ID Start_dt End_dt Month ActiveCustomerPerMonth
1 1/9/16 3/10/2016 201601 1
1 1/9/16 3/10/2016 201602 1
1 1/9/16 3/10/2016 201603 0 (Not Active end of Month)
我需要这个，因为我正在处理当前查询，该查询将使用案例语句来计算该月客户是否活跃。如果该成员在该月的最后一天处于活动状态，则该成员将被视为该月的活动成员。但是我需要能够为该客户算数个月的时间。
CASE
WHEN LAST_DAY(x.END_DT) = x.END_DT
THEN '1'
WHEN TO_CHAR(X.END_DT,'MM/DD/YYYY') != '01/01/3000'
OR X.DISCHARGE_REASON IS NOT NULL
THEN '0'
WHEN X.FIRST_ASSGN_DT IS NULL
THEN '0'
ELSE '1'
END ActiveMemberForMonth
我是Oracle的新手，正在阅读有关connect by的文章，但不了解该过程，也不确定这是否是合适的使用场所。
这样的事情。
with
test_data ( id, start_dt, end_dt ) as (
select 1, to_date('1/9/2016' , 'mm/dd/yyyy'), to_date('3/10/2016', 'mm/dd/yyyy')
from dual union all
select 2, to_date('1/23/2016', 'mm/dd/yyyy'), to_date('5/31/2016', 'mm/dd/yyyy')
from dual
)
-- end of test data; solution (SQL query) begins below this line
select id, start_dt, end_dt,
to_char(add_months(trunc(start_dt, 'mm'), level - 1), 'yyyymm') as mth,
case when end_dt < last_day(end_dt)
and level = 1 + months_between(trunc(end_dt, 'mm'), trunc(start_dt, 'mm'))
then 0 else 1 end as active_at_month_end
from test_data
connect by level <= 1 + months_between(trunc(end_dt, 'mm'), trunc(start_dt, 'mm'))
and prior id = id
and prior sys_guid() is not null
order by id, mth -- optional
;
ID START_DT END_DT MTH ACTIVE_AT_MONTH_END
--- ---------- ---------- ------ -------------------
1 2016-01-09 2016-03-10 201601 1
1 2016-01-09 2016-03-10 201602 1
1 2016-01-09 2016-03-10 201603 0
2 2016-01-23 2016-05-31 201601 1
2 2016-01-23 2016-05-31 201602 1
2 2016-01-23 2016-05-31 201603 1
2 2016-01-23 2016-05-31 201604 1
2 2016-01-23 2016-05-31 201605 1
8 rows selected.
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句