with t as(SELECT t.*FROM SCGL."JINEIXIANAN_SHENGCHAN" t where to_date(t.createdate,'yyyy-mm-dd')>=(select
(case when(to_date('${aa}','yyyy-mm-dd')>=trunc(to_date('${aa}','yyyy-mm-dd'),'YYYY') and to_date('${aa}','yyyy-mm-dd')<=
(trunc(to_date('${aa}','yyyy-mm-dd'),'YYYY')+28)) then trunc(to_date('${aa}','yyyy-mm-dd'),'YYYY') when (last_day(to_date
('${aa}','yyyy-mm-dd'))-to_date('${aa}','yyyy-mm-dd')) >1 or (to_date('${aa}','yyyy-mm-dd')>=last_day(ADD_MONTHS(trunc(to_date('${aa}','yyyy-mm-dd'),'YYYY'),11))-1 and to_date('${aa}','yyyy-mm-dd')<=last_day(ADD_MONTHS(trunc(to_date('${aa}','yyyy-mm-dd'),'YYYY'),11)))
then (last_day(ADD_MONTHS (to_date('${aa}','yyyy-mm-dd'),-1))-1) else (last_day(ADD_MONTHS (to_date('${aa}','yyyy-mm-dd'),0))-1) end) from
dual ) -1
and to_date(t.createdate,'yyyy-mm-dd') =(last_day(ADD_MONTHS(trunc
(to_date('${aa}','yyyy-mm-dd'),'YYYY'),11))-32) and to_date('${aa}','yyyy-mm-dd')<=last_day(ADD_MONTHS(trunc(to_date
('${aa}','yyyy-mm-dd'),'YYYY'),11))) then last_day(ADD_MONTHS(trunc(to_date('${aa}','yyyy-mm-dd'),'YYYY'),11)) when (last_day(to_date('${aa}','yyyy-mm-dd'))-to_date('${aa}','yyyy-
mm-dd'))>1 then (last_day(ADD_MONTHS (to_date('${aa}','yyyy-mm-dd'),0))-2) else (last_day(ADD_MONTHS (to_date('${aa}','yyyy-mm-dd'),1))-2) end)
as lastday from dual) order by t.id ),
t1 as (select rownum as rn,t.* from t)
select a.* from t1 a where (case when ( to_date(a.createdate,'yyyy-mm-dd') =(select (case when (to_date('${aa}','yyyy-mm-dd')>=(last_day(ADD_MONTHS(trunc
(to_date('${aa}','yyyy-mm-dd'),'YYYY'),11))-32) and to_date('${aa}','yyyy-mm-dd')<=last_day(ADD_MONTHS(trunc(to_date
('${aa}','yyyy-mm-dd'),'YYYY'),11))) then last_day(ADD_MONTHS(trunc(to_date('${aa}','yyyy-mm-dd'),'YYYY'),11)) when (last_day(to_date('${aa}','yyyy-mm-dd'))-to_date('${aa}','yyyy-
mm-dd'))>1 then (last_day(ADD_MONTHS (to_date('${aa}','yyyy-mm-dd'),0))-2) else (last_day(ADD_MONTHS (to_date('${aa}','yyyy-mm-dd'),1))-2) end)
as lastday from dual)) then a.rn1
