hi ,
i have belw code that is working fine, i just need to combine it in one single statement.
select number,
order_date
START_DATE,
END_DATE,
row_number() over (partition by number order by order_date asc) cnt,
next_date as SLS_next_date
into #SLS_MNTHLY
from(
select fb.*,
sls.order_date,
sls.next_date,
DEFAULT_STATUS
from dbo.ORDER_HIST SLS with(nolock) join #FB_PLAN_START_STOP_V2 fb with(nolock)
on fb.number = sls.number
where
sls.SNAPSHOT_DATE > END_DATE
and CATEGORY = '01. CURR/PP'
) a
where order_dateE is not null
drop table if exists #POST_PAYMENTS
select sls.number ,
SLM.LOAN_TYPE_DESC,
sls.START_DATE,
sls.END_DATE,
sls.order_date as POST_order_date,
SLS.next_date as POST_next_date,
SLM.next_date,
datediff (mm,SLS.SLS_next_date,SLM.next_date) as POST_FB_PAY
into #POST_PAYMENTS
from #SLS_MNTHLY SLS with(nolock) join dbo.sss SLM with(nolock)
on sls.number = SLM.number
where sls.cnt = 1
---------------------------------------------------------
drop table if exists #SLS_MNTHLY_PRE
select number,
order_date
START_DATE,
END_DATE,
row_number() over (partition by number order by order_dateE desc) cnt,
next_date as SLS_next_date
into #SLS_MNTHLY_PRE
from(
select fb.*,
sls.order_date,
sls.next_date,
DEFAULT_STATUS
from dbo.ORDER_HIST SLS with(nolock) join #FB_PLAN_START_STOP_V2 fb with(nolock)
on fb.number = sls.number
where
sls.SNAPSHOT_DATE < START_DATE
and CATEGORY = '01. CURR/PP'
) a
where order_dateE is not null
drop table if exists #PRE_PAYMENTS
select sls.number ,
SLM.LOAN_TYPE_DESC,
sls.START_DATE,
sls.END_DATE,
sls.order_date as POST_order_date,
SLS.next_date as POST_next_date,
SLM.next_date,
datediff (mm,SLS.SLS_next_date,SLM.next_date) as POST_FB_PAY
into #PRE_PAYMENTS
from #SLS_MNTHLY_PRE SLS with(nolock) join dbo.sss SLM with(nolock)
on sls.number = SLM.number
where sls.cnt = 1