SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Column with Yesterday values


Column with Yesterday values

Author
Message
ArjunaReddy
ArjunaReddy
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 554
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.
Jack Corbett
  Jack Corbett
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19486 Visits: 14900
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search