|
imfairozkhan
Starting Member
11 Posts |
 Posted - 2009-02-22 : 03:15:10
|
| Hi Guys below is the query it is taking around 30-40 secs how do i improve the performance. any help is highly appreciatedselect ccy,WLSPL.FInalBNPL-isnull(sum(case when (revaltypeid='60' or revaltypeid='61' or revaltypeid='69') then UnRealisedPL else 0 end),0) BNNotes,WLSPL.FinalFXPL-isnull(sum(case when (revaltypeid='65' or revaltypeid='66' or revaltypeid='67' or revaltypeid='68' or revaltypeid='59') then UnRealisedPL else 0 end),0) FXNotes,isnull(max(case when (revaltypeid='60' or revaltypeid='61' or revaltypeid='69') then revalrate else 0 end),0) BNRate,isnull(max(case when (revaltypeid='65' or revaltypeid='66' or revaltypeid='67' or revaltypeid='68' or revaltypeid='59') then revalrate else 0 end),0) FXRatefrom (SELECT ISNULL(P.xposn_description, 0) AS POSN_DESCRIPTION, C.ccy_code AS CCY, D.RevalTypeID, C.ccy_name AS CcyName, C.ccy_internal_code As CcyIntCode, POSN_TYPE, C.ccy_decm_plc As Decimals, D.InAmt, D.InEquivAmt, D.OutAmt, D.OutEquivAmt, SODPosition + OutAmt - InAmt AS TradedAmt, SODCalcAmt + OutEquivAmt - InEquivAmt AS TradedEquivAmt, ROUND(SODCalcAmt + OutEquivAmt - InEquivAmt, 3) AS CalcUnRInOutAmt, (SODCalcAmt + OutEquivAmt - InEquivAmt) /(SODPosition + OutAmt - InAmt) AS EODCustRate, SODPosition + OutAmt - InAmt AS EODPosition, ROUND(CASE WHEN ccyr_rate <> 0 THEN (SODPosition + OutAmt - InAmt) * (case when (d.revaltypeid = '65' or d.revaltypeid = '66' or d.revaltypeid = '67' or d.revaltypeid = '68') then (select top 1 ccyr_rate from wcs.dbo.ccyr where ccyr.ccyr_ccy_code=c.ccy_code and (ccyr.ccyr_ccyt_type = '03' or ccyr.ccyr_ccyt_type = '04') order by ccyr_date_time desc) else (select top 1 ccyr_rate from wcs.dbo.ccyr where ccyr.ccyr_ccy_code=c.ccy_code and (ccyr.ccyr_ccyt_type = '01' or ccyr.ccyr_ccyt_type = '02') order by ccyr_date_time desc) end) ELSE 0 END, 3) AS EODCalcAmt, 0 AS SODPosition, 0 AS SODRate, 0 AS SODCalcAmt, round( (SODCalcAmt + OutEquivAmt - InEquivAmt) - CASE WHEN ccyr_rate <> 0 THEN (SODPosition + OutAmt - InAmt)* (case when (d.revaltypeid = '65' or d.revaltypeid = '66' or d.revaltypeid = '67' or d.revaltypeid = '68') then (select top 1 ccyr_rate from wcs.dbo.ccyr where ccyr.ccyr_ccy_code=c.ccy_code and (ccyr.ccyr_ccyt_type = '03' or ccyr.ccyr_ccyt_type = '04') order by ccyr_date_time desc) else (select top 1 ccyr_rate from wcs.dbo.ccyr where ccyr.ccyr_ccy_code=c.ccy_code and (ccyr.ccyr_ccyt_type = '01' or ccyr.ccyr_ccyt_type = '02') order by ccyr_date_time desc) end) ELSE 0 END ,3) AS UnRealisedPL, --RR.reval_rate_rounded AS RevalRate case when (d.revaltypeid = '65' or d.revaltypeid = '66' or d.revaltypeid = '67' or d.revaltypeid = '68') then (select top 1 ccyr_rate from wcs.dbo.ccyr where ccyr.ccyr_ccy_code=c.ccy_code and (ccyr.ccyr_ccyt_type = '03' or ccyr.ccyr_ccyt_type = '04') order by ccyr_date_time desc) else (select top 1 ccyr_rate from wcs.dbo.ccyr where ccyr.ccyr_ccy_code=c.ccy_code and (ccyr.ccyr_ccyt_type = '01' or ccyr.ccyr_ccyt_type = '02') order by ccyr_date_time desc) end as RevalRate, Row_Number() over(partition by P.xposn_description order by P.xposn_description asc) as RowNum FROM wcs.dbo.ccy C INNER JOIN wcs.dbo.ccyr RR ON C.ccy_code = RR.ccyr_ccy_codeINNER JOIN ( SELECT ISNULL(R.tran_ccy_code, P.CCY) AS CCY, ISNULL(R.RevalTypeID, P.RevalTypeID) AS RevalTypeID, P.Stock, CASE ISNULL(R.RevalTypeID, P.RevalTypeID) WHEN '59' THEN 'FXOH' WHEN '60' THEN 'COH' + CASE ISNULL(R.Stock, P.Stock) WHEN 1 THEN 'S' ELSE '' END WHEN '61' THEN 'CIT' + CASE ISNULL(R.Stock, P.Stock) WHEN 1 THEN 'S' ELSE '' END WHEN '62' THEN 'CSND' + CASE ISNULL(R.Stock, P.Stock) WHEN 1 THEN 'S' ELSE '' END WHEN '63' THEN 'NOH' WHEN '64' THEN 'NIT' WHEN '65' THEN 'SO' WHEN '66' THEN 'SD' WHEN '67' THEN 'BC' WHEN '68' THEN 'SC' WHEN '69' THEN 'CADJ' END As POSN_TYPE, -- values coming from the deals actioned today in some way SUM(CASE WHEN P.DIRECTION = 'I' THEN P.AMT ELSE 0 END) As InAmt, SUM(CASE WHEN P.DIRECTION = 'I' THEN P.BASE_EQUIV ELSE 0 END) As InEquivAmt, SUM(CASE WHEN P.DIRECTION = 'O' THEN P.AMT ELSE 0 END) As OutAmt, SUM(CASE WHEN P.DIRECTION = 'O' THEN P.BASE_EQUIV ELSE 0 END) As OutEquivAmt, SUM(ISNULL(ABS(P.AMT),0)) AS AmtGross, -- values coming from the most recent revaluation ISNULL(R.SODPosition,0) AS SODPosition, ISNULL(R.SODCalcAmt,0) AS SODCalcAmt FROM wcs.dbo.vwPL_LastRevals R FULL OUTER JOIN wcs.dbo.vwPL_AllocateDealsToPosn P ON R.tran_ccy_code = P.CCY AND P.RevalTypeID = R.RevalTypeID AND P.Stock = R.Stock WHERE NOT (P.AMT = 0 AND ISNULL(R.SODPosition,0) = 0 AND ISNULL(R.SODCalcAmt,0) = 0 ) GROUP BY R.tran_ccy_code, P.CCY, P.RevalTypeID, R.RevalTypeID, R.SODPosition, R.SODCalcAmt, P.Stock, R.Stock HAVING (ISNULL(R.SODPosition,0) + SUM(CASE WHEN P.DIRECTION = 'O' THEN P.AMT ELSE 0 END) - SUM(CASE WHEN P.DIRECTION = 'I' THEN P.AMT ELSE 0 END)) <> 0) D ON C.ccy_code = D.CCY LEFT JOIN wcs.dbo.xposn P ON D.POSN_TYPE = P.xposn_posn_type AND P.xposn_cnty_code = wcs.dbo.udfGetInstValue('base_cnty')WHERE C.ccy_code = ISNULL('AED', C.ccy_code)and (ccyr_ccyt_type='01' or ccyr_ccyt_type='02' or ccyr_ccyt_type='03' or ccyr_ccyt_type='04')) OnlinePandL inner join MIS.DBO.WLSPL as WLSPL on OnlinePandL.ccy=WLSPL.FinalCur where RowNum=1 and convert(varchar,WLSPL.FinalDate,103)=convert(varchar,getdate()-1,103)group by ccy,WLSPL.FinalBNPL,FinalFXPL |
|
Improve Performance