April 24, 2013 at 6:56 am
Hi,
Please help me out with the below query.
We are calculating Exposure column (Hilighted in the query) based on current date and my requirement now is to add a new column next to it with yesterdays values (Currentdate-1). Please help me out.
SQLServer 2008 R2
-----------------------------
SELECT --bi.cobdate,
fact.batchid,
fact.mastergroup,
fact.counterparty,
fact.counterpartyname,
fact.parentcounterpartyname AS
ParentCounterparty --If No Parent, then show the original CP as Parent
,
fact.portfoliolevelcd AS
AggregationScheme,
fact.portfolionodevalue AggregationNode,
ptycc.anzccr AS CCR,
ptycc.securityindicator AS SI,
fact.limittimeband,
fact.limitstartdt AS [Start_Date],
fact.limitenddt AS [End_Date],
COALESCE(ext.currency, fact.limitcurrency) AS LocalCurrency,
fact.limitcurrency AS LimitCCY,
COALESCE(fun.currency, fun1.limitcurrency) LocalExchnagerate,
fun1.limitcurrency AS
LimitExchnagerate,
( fun1.curvepointvalue / fun.curvepointvalue ) *
CONVERT(FLOAT, fact.limitamount) AS Limit
--,fun.CurvePointValue LocalCurrency
,
fun1.curvepointvalue Limitcurrency,
pfe.riskvalue AS Exposure
--,pfe.RiskValue2 as "ExposureT-1"
,
( ( fun1.curvepointvalue / fun.curvepointvalue ) *
CONVERT(FLOAT, fact.limitamount) - pfe.riskvalue ) AS Availability,
( CASE
WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) *
CONVERT(FLOAT, fact.limitamount) ) IS NULL
OR pfe.riskvalue IS NULL THEN 0
WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) *
CONVERT(FLOAT, fact.limitamount) ) = 0
AND pfe.riskvalue > 0 THEN 1
WHEN ( ( fun1.curvepointvalue / fun.curvepointvalue ) *
CONVERT(FLOAT, fact.limitamount) ) = 0
AND pfe.riskvalue = 0 THEN 0
ELSE Cast(( Isnull(pfe.riskvalue, 0) / (
( fun1.curvepointvalue / fun.curvepointvalue ) *
CONVERT(
FLOAT, fact.limitamount) ) )
AS FLOAT)
END ) AS Utilisation,
ptycc.creditcontrolpoint AS ControlPoint,
fact.[CollateralAgreementCd] CollateralApplied,
fact.[NettingAgreementCd] NettingApplied,
fact.israzor
FROM dw.[factlimitutilizationbyportfolio] FACT (nolock)
INNER JOIN (SELECT br.batchrunid BatchID,
bi.businessdate CobDate,
bi.batchinstanceid,
br.startdatetime AS ReportingDate
FROM logging.batchrun br (nolock)
INNER JOIN logging.batchinstance bi (nolock)
ON br.batchinstanceid = bi.batchinstanceid) BI
ON fact.batchid = Bi.batchid
INNER JOIN dw.partycreditcontrol ptycc (nolock)
ON fact.counterpartyid = ptycc.partyid
AND ( ptycc.effstartdate <= bi.cobdate
AND ptycc.effenddate > bi.cobdate )
INNER JOIN dw.portfolio port (nolock)
ON fact.portfolioid = port.portfolioid
AND port.providersystemcd = 'Razor'
AND port.portfoliolevelcd = 'Customer Asset Group'
AND port.effstartdate <= bi.cobdate
AND port.effenddate > bi.cobdate
LEFT JOIN dw.portfoliobridge bport (nolock)
ON ( bport.tgtportfolioid = fact.portfolioid
AND bport.tgtprovidercd = 'Razor'
AND bport.effstartdate <= bi.cobdate
AND bport.effenddate > bi.cobdate )
LEFT JOIN (SELECT t.portfolioid,
t.limittimeband,
Max(t.maxexposure) AS RiskValue,
t.batchid
FROM dw.factlimitutilizationbyportfolio t (nolock)
INNER JOIN dw.riskmeasuredefinition rmd (nolock)
ON t.riskmeasuredefinitionid
= rmd.riskmeasureid
AND rmd.riskmeasurename = 'PFE_LC'
AND t.isfact = 1
GROUP BY t.portfolioid,
t.limittimeband,
t.batchid) pfe
ON bport.srcportfolioid = pfe.portfolioid
AND bport.srcprovidercd = 'CRE'
AND fact.limittimeband = pfe.limittimeband
AND fact.batchid = PFE.batchid
LEFT JOIN (SELECT DISTINCT portfolioid,
currency,
runid
FROM extract.razorportfoliotraderelation)ext
ON fact.batchid = ext.runid
AND PFE.portfolioid = ext.portfolioid
LEFT JOIN (SELECT cpt.curvepointvalue,
cdt.observationdt,
cid.curveidentifier currency
FROM dw.curveidentifier cid (nolock)
INNER JOIN dw.curvedata cdt (nolock)
ON cid.curveid = cdt.curveid
INNER JOIN dw.curvepoint cpt (nolock)
ON cdt.curvedataid = cpt.curvedataid
WHERE cid.curvetype = 'Exchange'
AND cid.curvedomain = 'QuIC'
AND cid.islatest = 1
AND cdt.islatest = 1
AND cpt.islatest = 1
AND cdt.ccycd = 'USD') fun
ON fun.observationdt = bi.cobdate
AND fun.currency = ext.currency
LEFT JOIN (SELECT cpt.curvepointvalue,
cdt.observationdt,
cid.curveidentifier LimitCurrency
FROM dw.curveidentifier cid (nolock)
INNER JOIN dw.curvedata cdt (nolock)
ON cid.curveid = cdt.curveid
INNER JOIN dw.curvepoint cpt (nolock)
ON cdt.curvedataid = cpt.curvedataid
WHERE cid.curvetype = 'Exchange'
AND cid.curvedomain = 'QuIC'
AND cid.islatest = 1
AND cdt.islatest = 1
AND cpt.islatest = 1
AND cdt.ccycd = 'USD') fun1
ON fun1.observationdt = bi.cobdate
AND fun1.limitcurrency = fact.limitcurrency
WHERE isfact = 0
-----------------------------
Thanks,
Nagarjun.
April 24, 2013 at 7:21 am
I'd start with something like this which is the most common way to get a previous value:
WITH ctePFE
AS (
SELECT
t.portfolioid,
t.limittimeband,
MAX(t.maxexposure) AS RiskValue,
t.batchid,
ROW_NUMBER() OVER (PARTITION BY t.portfolioid, t.limittimeband, t.batchid ORDER BY t.timeband) AS rowNo
FROM
dw.factlimitutilizationbyportfolio t (NOLOCK)
INNER JOIN dw.riskmeasuredefinition rmd (NOLOCK)
ON t.riskmeasuredefinitionid = rmd.riskmeasureid AND
rmd.riskmeasurename = 'PFE_LC' AND
t.isfact = 1
GROUP BY
t.portfolioid,
t.limittimeband,
t.batchid
),
ctePFEwithPreviousValue
AS (
SELECT
PFE1.portfolioid,
PFE1.limittimeband,
PFE1.RiskValue,
PFE1.batchid,
PFE2.RiskValue AS previousRiskValue
FROM
ctePFE AS pFE1
LEFT JOIN ctePFE AS PFE2
ON PFE1.portfolioid = PFE2.portfolioid AND
pFE1.rowNo = PFE2.rowNo + 1
)
SELECT --bi.cobdate,
fact.batchid,
fact.mastergroup,
fact.counterparty,
fact.counterpartyname,
fact.parentcounterpartyname AS ParentCounterparty --If No Parent, then show the original CP as Parent
,
fact.portfoliolevelcd AS AggregationScheme,
fact.portfolionodevalue AggregationNode,
ptycc.anzccr AS CCR,
ptycc.securityindicator AS SI,
fact.limittimeband,
fact.limitstartdt AS [Start_Date],
fact.limitenddt AS [End_Date],
COALESCE(ext.currency, fact.limitcurrency) AS LocalCurrency,
fact.limitcurrency AS LimitCCY,
COALESCE(fun.currency, fun1.limitcurrency) LocalExchnagerate,
fun1.limitcurrency AS LimitExchnagerate,
(fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount) AS Limit
--,fun.CurvePointValue LocalCurrency
,
fun1.curvepointvalue Limitcurrency,
pfe.riskvalue AS Exposure
--,pfe.RiskValue2 as "ExposureT-1"
,
((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount) - pfe.riskvalue) AS Availability,
(CASE WHEN ((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount)) IS NULL OR
pfe.riskvalue IS NULL THEN 0
WHEN ((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount)) = 0 AND
pfe.riskvalue > 0 THEN 1
WHEN ((fun1.curvepointvalue / fun.curvepointvalue) * CONVERT(FLOAT, fact.limitamount)) = 0 AND
pfe.riskvalue = 0 THEN 0
ELSE CAST((ISNULL(pfe.riskvalue, 0) / ((fun1.curvepointvalue / fun.curvepointvalue) *
CONVERT(FLOAT, fact.limitamount))) AS FLOAT)
END) AS Utilisation,
ptycc.creditcontrolpoint AS ControlPoint,
fact.[CollateralAgreementCd] CollateralApplied,
fact.[NettingAgreementCd] NettingApplied,
fact.israzor
FROM
dw.[factlimitutilizationbyportfolio] FACT (NOLOCK)
INNER JOIN (
SELECT
br.batchrunid BatchID,
bi.businessdate CobDate,
bi.batchinstanceid,
br.startdatetime AS ReportingDate
FROM
logging.batchrun br (NOLOCK)
INNER JOIN logging.batchinstance bi (NOLOCK)
ON br.batchinstanceid = bi.batchinstanceid
) BI
ON fact.batchid = Bi.batchid
INNER JOIN dw.partycreditcontrol ptycc (NOLOCK)
ON fact.counterpartyid = ptycc.partyid AND
(
ptycc.effstartdate <= bi.cobdate AND
ptycc.effenddate > bi.cobdate
)
INNER JOIN dw.portfolio port (NOLOCK)
ON fact.portfolioid = port.portfolioid AND
port.providersystemcd = 'Razor' AND
port.portfoliolevelcd = 'Customer Asset Group' AND
port.effstartdate <= bi.cobdate AND
port.effenddate > bi.cobdate
LEFT JOIN dw.portfoliobridge bport (NOLOCK)
ON (
bport.tgtportfolioid = fact.portfolioid AND
bport.tgtprovidercd = 'Razor' AND
bport.effstartdate <= bi.cobdate AND
bport.effenddate > bi.cobdate
)
LEFT JOIN ctePFEwithPreviousValue pfe
ON bport.srcportfolioid = pfe.portfolioid AND
bport.srcprovidercd = 'CRE' AND
fact.limittimeband = pfe.limittimeband AND
fact.batchid = PFE.batchid
LEFT JOIN (
SELECT DISTINCT
portfolioid,
currency,
runid
FROM
extract.razorportfoliotraderelation
) ext
ON fact.batchid = ext.runid AND
PFE.portfolioid = ext.portfolioid
LEFT JOIN (
SELECT
cpt.curvepointvalue,
cdt.observationdt,
cid.curveidentifier currency
FROM
dw.curveidentifier cid (NOLOCK)
INNER JOIN dw.curvedata cdt (NOLOCK)
ON cid.curveid = cdt.curveid
INNER JOIN dw.curvepoint cpt (NOLOCK)
ON cdt.curvedataid = cpt.curvedataid
WHERE
cid.curvetype = 'Exchange' AND
cid.curvedomain = 'QuIC' AND
cid.islatest = 1 AND
cdt.islatest = 1 AND
cpt.islatest = 1 AND
cdt.ccycd = 'USD'
) fun
ON fun.observationdt = bi.cobdate AND
fun.currency = ext.currency
LEFT JOIN (
SELECT
cpt.curvepointvalue,
cdt.observationdt,
cid.curveidentifier LimitCurrency
FROM
dw.curveidentifier cid (NOLOCK)
INNER JOIN dw.curvedata cdt (NOLOCK)
ON cid.curveid = cdt.curveid
INNER JOIN dw.curvepoint cpt (NOLOCK)
ON cdt.curvedataid = cpt.curvedataid
WHERE
cid.curvetype = 'Exchange' AND
cid.curvedomain = 'QuIC' AND
cid.islatest = 1 AND
cdt.islatest = 1 AND
cpt.islatest = 1 AND
cdt.ccycd = 'USD'
) fun1
ON fun1.observationdt = bi.cobdate AND
fun1.limitcurrency = fact.limitcurrency
WHERE
isfact = 0
This solution probably won't work out of the box because I don't know the database to know what all the columns mean, but the key point is the CTE with row_number. If you get ROW_NUMBER configured correctly the JOIN on rowNO = rowNO + 1 get's you the previous value if you go with the default Ascending sort order.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply