combine code

Question

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

Answer 1

Hi @coool sweet ,

Welcome to microsoft TSQL Q&A forum!

I am also confused: why do you need a separate sql statement? For your question, the alternative is to use cte, but as ErlandSommarskog mentioned, the efficiency of temporary tables is actually higher than cte.

Please also check:

;with cte as  
( 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')   
  
select into #POST_PAYMENTS  
from cte SLS with(nolock) join dbo.sss SLM with(nolock)  
on sls.number = SLM.number  
where sls.cnt = 1  
  
  
;with cte2 as(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')  
  
select into #PRE_PAYMENTS  
from cte2 SLS with(nolock) join dbo.sss SLM with(nolock)  
on sls.number = SLM.number  
where sls.cnt = 1  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

What makes you think that you need a single statement?

You can achieve this by replacing the temp tables with CTEs (Common Table Expressions)

WITH SLS_MNTHLY AS (
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
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
),  POST_PAYMENTS AS ( 
  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

from SLS_MNTHLY SLS with(nolock) join dbo.sss SLM with(nolock)
on sls.number = SLM.number
where sls.cnt = 1 
),  andsoon

But you may find that it is more difficult to debug, and that performance degrades. CTEs are great, but sometimes you can have too many of them.

Answer 3

i need one single select statement

Answer 4

Consider this approach:

begin tran

. . . your statements . . .

commit