April 24, 2013 at 7:02 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 8:52 am
ArjunaReddy (4/24/2013)
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.
-----------------------------
Thanks,
Nagarjun.
So you posted a query with dozens of tables and are asking for somebody to help. We don't know your system or your data, we can't see what you see. There is no chance that anybody will be able to help you without a LOT more information here. Your Exposure column comes from a subselect that doesn't have any date calculations. Honestly the request here seems to me to be more than you can expect from an online forum.
As a side note....why all the NOLOCK hints? Are you aware of what that hint does?
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2013 at 8:52 am
Here is the original query after formatting:
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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2013 at 3:03 pm
This looks like a snowflake schema with a chain of slowly-changing dimensions.
You'll need to map how the original pfe subquery joins to the other subqueries and trace it back to the fact table.
Then, replicate the required subqueries, modifying the effstartdate and effenddate conditions to show yesterday.
This is a great opportunity for you to fully understand your database schema; it should be straightforward, albeit complicated-looking at first.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply