Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
jcarrallo
Starting Member
35 Posts |
Posted - 2014-06-21 : 05:39:22
|
Hi SQL Team,Thanks for your invaluable help.I have the select statement below - where I use some external functions - and I would like the result to be saved to another table.After executing the statement I end up with a table containing the following columns:accountnod.date_startd.date_endTWRR_MonthTWRR_CumNormally I would use the command 'INTO TableName' but since the statement is so long I don´t know where to place it.Please help!Jay The select statement is as follows: IF OBJECT_ID('tempdb..#trn') IS NOT NULL DROP TABLE #trnIF OBJECT_ID('tempdb..#mv') IS NOT NULL DROP TABLE #mvSELECT PTR_sequence as trno, PTR_CLIENTACCOUNTNUMBER as accountno, PTR_DATE as date_trn, CASE PTR_TAC WHEN 'BUY' THEN 0 ELSE PTR_LOCALAMT END as amt_trnINTO #trnFROM MPR_Portfolio_TransactionsSELECT PVL_CLIENTACCOUNTNUMBER as accountno, PVL_DATE as date_mv, PVL_Value as amt_mvINTO #mvFROM MPR_Portfolio_Valuations;with mycte(accountno,dt,cf,mv) as ( SELECT accountno ,date_mv ,amt_mv ,'True' FROM #mv UNION ALL SELECT accountno ,date_trn ,amt_trn ,NULL FROM #trn) SELECT d.accountno,d.date_start,d.date_end,wct.GTWRR(t1.cf,t1.dt,t1.mv,2) as TWRR_Month,wct.GTWRR(t2.cf,t2.dt,t2.mv,2) as TWRR_CumFROM ( SELECT m1.accountno as accountno ,m1.date_mv as date_start ,MIN(m2.date_mv) as date_end FROM #mv m1 JOIN #mv m2 ON m1.accountno = m2.accountno AND m2.date_mv > m1.date_mv GROUP BY m1.date_mv ,m1.accountno ) dJOIN mycte t1ON t1.accountno = d.accountnoAND t1.dt between d.date_start and d.date_endJOIN mycte t2ON t2.accountno = d.accountno ANDt2.dt between '2011-12-31' and d.date_endGROUP BY d.accountno, d.date_start, d.date_endThanks in advanced.Jay |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-06-21 : 06:15:57
|
Like below assuming you dont have table already existingOtherwise you need to use INSERT ...SELECTIF OBJECT_ID('tempdb..#trn') IS NOT NULLDROP TABLE #trnIF OBJECT_ID('tempdb..#mv') IS NOT NULLDROP TABLE #mvSELECT PTR_sequence as trno, PTR_CLIENTACCOUNTNUMBER as accountno, PTR_DATE as date_trn, CASE PTR_TAC WHEN 'BUY' THEN 0ELSE PTR_LOCALAMTEND as amt_trnINTO #trnFROM MPR_Portfolio_TransactionsSELECT PVL_CLIENTACCOUNTNUMBER as accountno, PVL_DATE as date_mv, PVL_Value as amt_mvINTO #mvFROM MPR_Portfolio_Valuations;with mycte(accountno,dt,cf,mv) as (SELECT accountno,date_mv,amt_mv,'True'FROM #mvUNION ALLSELECT accountno,date_trn,amt_trn,NULLFROM #trn) SELECT d.accountno,d.date_start,d.date_end,wct.GTWRR(t1.cf,t1.dt,t1.mv,2) as TWRR_Month,wct.GTWRR(t2.cf,t2.dt,t2.mv,2) as TWRR_CumINTO YourTableNameFROM (SELECT m1.accountno as accountno,m1.date_mv as date_start,MIN(m2.date_mv) as date_endFROM #mv m1JOIN #mv m2ON m1.accountno = m2.accountnoAND m2.date_mv > m1.date_mvGROUP BY m1.date_mv,m1.accountno) dJOIN mycte t1ON t1.accountno = d.accountnoAND t1.dt between d.date_start and d.date_endJOIN mycte t2ON t2.accountno = d.accountno ANDt2.dt between '2011-12-31' and d.date_endGROUP BY d.accountno, d.date_start, d.date_end ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jcarrallo
Starting Member
35 Posts |
Posted - 2014-06-21 : 06:21:35
|
Perfect!!!many thanksJay |
|
|
|
|
|
|
|