question

cooolsweet-5113 avatar image
0 Votes"
cooolsweet-5113 asked EchoLiu-msft commented

combine code

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

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Evaluate the differences between the PRE and POST queries. The key differences are in the where clause and the row_number - once you identify that you can combine that into a single query using a CASE expression to generate the count. Maybe: CASE WHEN sls.SNAPSHOT_DATE < START_DATE THEN row_number() over(...) WHEN sls.SNAPSHOT_DATE > END_DATE THEN row_number() over(...) ELSE 0 END AS cnt. Add another column to identify PRE/POST...this should get you to one CTE and the final query from that CTE.

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

Consider this approach:

begin tran

. . . your statements . . .

commit


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooolsweet-5113 avatar image
0 Votes"
cooolsweet-5113 answered Viorel-1 commented

i need one single select statement

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @cooolsweet-5113,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.