GAMES_USER_OFFERUSER_OFFER_ID START_DATE END_DATE STATUS
380 13/06/2005 4:02:17 PM 01/05/2007 4:15:43 PM A
521 28/06/2005 12:18:08 PM 01/05/2007 12:18:08 PM I
需要查询的结果如下 ACT NON ACT
Less 1 month
30-60
60-90
3 month-6 months
6 month-12 months
More than 1 year Less 1 month ,30-60, 60-90为END_DATE -START_DATE的天数范围
ACT,NON ACT 为STATUS为A,I时候得状态查询GAMES_USER_OFFER表小于一个月,30-60天,60-90天,3 month-6 months,6 month-12 months,More than 1 year状态为A,为I总USER_OFFER_ID
380 13/06/2005 4:02:17 PM 01/05/2007 4:15:43 PM A
521 28/06/2005 12:18:08 PM 01/05/2007 12:18:08 PM I
需要查询的结果如下 ACT NON ACT
Less 1 month
30-60
60-90
3 month-6 months
6 month-12 months
More than 1 year Less 1 month ,30-60, 60-90为END_DATE -START_DATE的天数范围
ACT,NON ACT 为STATUS为A,I时候得状态查询GAMES_USER_OFFER表小于一个月,30-60天,60-90天,3 month-6 months,6 month-12 months,More than 1 year状态为A,为I总USER_OFFER_ID
from(
select flag,status,count(*)
from
(
select case
when timespan<30 then 'Less 1 month'
when timespan>=30 and timespan<60 then '30-60'
.....
else 'More than 1 year'
end as flag,STATUS
from
(select floor(START_DATE-END_DATE) timespan,STATUS from GAMES_USER_OFFER)
)
)
group by flag
select flag,max(decode(status,'A',c,'0')) A,max(decode(status,'I',c,'0')) I
from(
select flag,status,count(*) c
from
(
select case
when timespan<30 then 'Less 1 month'
when timespan>=30 and timespan<60 then '30-60'
.......
else 'More than 1 year'
end as flag,STATUS
from
(select TRUNC(end_date)-TRUNC(start_DATE) timespan,STATUS from a)
)group by flag,status
)
group by flag