Column with Yesterday values

  • 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.

  • 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.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply