Selecting Min of multiple values, including an operation

  • daniness

    SSCrazy

    Points: 2890

    Hi All,

    It's been a while, but would like to tap on your expertise. I've been trying to figure out the below query, particularly the OriginalWrittenPremiumMTD field, which involves taking the minimum of 2 values, 1 of which involves a multiplication operation. Could you please advise on the easiest way to do this? Thanks in advance!

    SELECT DISTINCT

    ire.SourceId as EnrollmentID

    ,ip.PolicyNumber

    ,re.SourceTransactionId

    ,ip.PolicyCode

    ,p.ProductID

    ,ire.ContractNumber

    ,rec.CoverageType AS CoverageTypeName

    ,ac.AssetClassCode AS PCMAssetClassCode

    ,ac.AssetClassDescription AS PCMAssetClassDescription

    ,ip.ClientAddress1

    ,ip.ClientAddress2

    ,ip.ClientCity

    ,ip.ClientState

    ,ip.ClientPostalCode

    ,ip.ClientCountry

    ,ire.SourceCoveragePeriod AS CoverageID

    ,re.SourceCoverageStartDate

    ,re.SourceCoverageEndDate

    ,re.SourceCoverageTerm

    ,ire.CancelDate

    ,re.SourceCoveragePeriod

    ,re.SourceTransactionDate

    ,amp.BillingCenterFeedDate AS PeriodEndCalendar

    ,wp.InvoiceAccountingYearMonth AS AccountingPeriod

    ,Sum(pr.WrittenPremium) AS OriginalWrittenPremiumITD

    ,Sum(pr.WrittenPremium)/MIN(pr.Term, (12 * wp.AnnualIncrement)) AS OriginalWrittenPremiumMTD

    ,

    Sum(pr.CanceledPremium) AS CancelledPremiumMTD

    FROM ExtFSEInvoicePolicy ip

    INNER JOIN ExtFSEInvoiceRiskEntity ire ON ip.PCMPolicyID = ire.PcmPolicyId

    INNER JOIN ExtFSEInvoiceRiskEntityCoverage rec ON ire.RiskEntityId = rec.RiskEntityId

    INNER JOIN ExtFSERiskItem ri ON rec.RiskEntityId = ri.RiskEntityId

    INNER JOIN ExtPCMAssetClass ac ON ri.PcmAssetClassId = ac.AssetClassId

    INNER JOIN ExtFSERiskEntity re ON ri.RiskEntityId = re.Id

    INNER JOIN ExtFSEPercentPremiumReported pr ON ri.PcmCoverageId = pr.PcmCoverageId

    INNER JOIN ExtFSEAccountMonthPolicy amp ON ip.PCMPolicyID = amp.PcmPolicyId

    INNER JOIN ExtFSEWrittenPremium wp ON re.id = wp.RiskEntityId

    INNER JOIN ExtPCMProduct p ON ip.ProductName = p.ProductName
    GROUP By wp.RiskEntityId,

    ire.SourceId,

    ire.SourceCoveragePeriod,

    ip.PolicyNumber,

    re.SourceTransactionId,

    ip.PolicyCode,

    p.ProductId,

    ire.ContractNumber,

    rec.CoverageType,

    ac.AssetClassCode,

    ac.AssetClassDescription,

    ip.ClientAddress1,

    ip.ClientAddress2,

    ip.ClientCity,

    ip.ClientState,

    ip.ClientPostalCode,

    ip.ClientCountry,

    re.SourceCoveragePeriod,

    re.SourceCoverageStartDate,

    re.SourceCoverageEndDate,

    re.SourceCoverageTerm,

    ire.CancelDate,

    re.SourceCoveragePeriod,

    re.SourceTransactionDate,

    amp.BillingCenterFeedDate,

    wp.InvoiceAccountingYearMonth,

    pr.EarnedPremium,

    wp.TransactionTypeId,

    pr.MonthlyEarnedPremium,

    wp.InvoiceAmount

    ORDER BY ire.ContractNumber, ac.AssetClassCode

  • ScottPletcher

    SSC Guru

    Points: 98441

    Not 100% sure of the specific MIN rules, but something like this should do it, and far more efficiently:


    SELECT DISTINCT

    ire.SourceId as EnrollmentID

    ,ip.PolicyNumber

    ,re.SourceTransactionId

    ,ip.PolicyCode

    ,p.ProductID

    ,ire.ContractNumber

    ,rec.CoverageType AS CoverageTypeName

    ,ac.AssetClassCode AS PCMAssetClassCode

    ,ac.AssetClassDescription AS PCMAssetClassDescription

    ,ip.ClientAddress1

    ,ip.ClientAddress2

    ,ip.ClientCity

    ,ip.ClientState

    ,ip.ClientPostalCode

    ,ip.ClientCountry

    ,ire.SourceCoveragePeriod AS CoverageID

    ,re.SourceCoverageStartDate

    ,re.SourceCoverageEndDate

    ,re.SourceCoverageTerm

    ,ire.CancelDate

    ,re.SourceCoveragePeriod

    ,re.SourceTransactionDate

    ,amp.BillingCenterFeedDate AS PeriodEndCalendar

    ,wp.InvoiceAccountingYearMonth AS AccountingPeriod

    ,pr.OriginalWrittenPremiumITD

    ,pr.OriginalWrittenPremiumITD / CASE WHEN pr.MinTerm <= (12 * wp.AnnualIncrement) THEN pr.MinTerm ELSE (12 * wp.AnnualIncrement) END AS OriginalWrittenPremiumMTD

    ,pr.CancelledPremiumMTD

    FROM ExtFSEInvoicePolicy ip

    INNER JOIN ExtFSEInvoiceRiskEntity ire ON ip.PCMPolicyID = ire.PcmPolicyId

    INNER JOIN ExtFSEInvoiceRiskEntityCoverage rec ON ire.RiskEntityId = rec.RiskEntityId

    INNER JOIN ExtFSERiskItem ri ON rec.RiskEntityId = ri.RiskEntityId

    INNER JOIN ExtPCMAssetClass ac ON ri.PcmAssetClassId = ac.AssetClassId

    INNER JOIN ExtFSERiskEntity re ON ri.RiskEntityId = re.Id

    INNER JOIN (
      SELECT PcmCoverageId
      ,Sum(WrittenPremium) AS OriginalWrittenPremiumITD
      ,MIN(Term) AS MinTerm
      ,Sum(CanceledPremium) AS CancelledPremiumMTD
      FROM ExtFSEPercentPremiumReported
      GROUP BY PcmCoverageId
    ) AS pr ON ri.PcmCoverageId = pr.PcmCoverageId

    INNER JOIN ExtFSEAccountMonthPolicy amp ON ip.PCMPolicyID = amp.PcmPolicyId

    INNER JOIN ExtFSEWrittenPremium wp ON re.id = wp.RiskEntityId

    INNER JOIN ExtPCMProduct p ON ip.ProductName = p.ProductName
    ORDER BY ire.ContractNumber, ac.AssetClassCode

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • daniness

    SSCrazy

    Points: 2890

    Thanks, @scottpletcher ! I'll try to incorporate your suggestion into my query...you're brilliant :smooooth:

  • Eddie Wuerch

    SSChampion

    Points: 12380

    daniness - Friday, September 7, 2018 10:31 AM

    SELECT <...>

    ,Sum(pr.WrittenPremium) AS OriginalWrittenPremiumITD

    ,Sum(pr.WrittenPremium)/MIN(pr.Term, (12 * wp.AnnualIncrement)) AS OriginalWrittenPremiumMTD

    ,

    Sum(pr.CanceledPremium) AS CancelledPremiumMTD

    FROM ExtFSEInvoicePolicy ip

    <...>

    Just use Windowing Functions (any aggregate function may be used as a Windowing Function) :

    SELECT <...>

    ,Sum(pr.WrittenPremium) OVER (PARTITION BY PcmCoverageId) AS OriginalWrittenPremiumITD
    ,CASE WHEN MIN(pr.Term) OVER (PARTITION BY PcmCoverageId) <= (12 * wp.AnnualIncrement) THEN MIN(pr.Term) OVER (PARTITION BY PcmCoverageId) ELSE (12 * wp.AnnualIncrement) END AS OriginalWrittenPremiumMTD
    ,Sum(pr.CanceledPremium) OVER (PARTITION BY PcmCoverageId) AS CancelledPremiumMTD
    FROM ExtFSEInvoicePolicy ip

    <...>

    Eddie Wuerch
    MCM: SQL

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719896

    I prefer the windowing function in general, but you ought to test both, watching the number of logical reads each incurs.

  • Eddie Wuerch

    SSChampion

    Points: 12380

    Steve Jones - SSC Editor - Monday, September 10, 2018 7:50 AM

    I prefer the windowing function in general, but you ought to test both, watching the number of logical reads each incurs.

    I agree. An important point with Windowing Functions is that every aggregate performed with the same window criteria (PARTITION BY ... ORDER BY ...) clause are all executed together against the same aggregate set. For example, my script above has several windowed aggregates, but they are all performed against the same window set (PARTITION BY PcmCoverageId).

    But if you throw in more aggs on different windows, the query can easily fall down.
    Keep the number of different window sets to a minimum when using Windowing Functions.

    -Eddie

    Eddie Wuerch
    MCM: SQL

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    Eddie Wuerch - Saturday, September 15, 2018 10:16 PM

    Steve Jones - SSC Editor - Monday, September 10, 2018 7:50 AM

    I prefer the windowing function in general, but you ought to test both, watching the number of logical reads each incurs.

    I agree. An important point with Windowing Functions is that every aggregate performed with the same window criteria (PARTITION BY ... ORDER BY ...) clause are all executed together against the same aggregate set. For example, my script above has several windowed aggregates, but they are all performed against the same window set (PARTITION BY PcmCoverageId).

    But if you throw in more aggs on different windows, the query can easily fall down.
    Keep the number of different window sets to a minimum when using Windowing Functions.

    -Eddie

    The Window(ing) functions perform well when applied to sets of relatively moderate cardinality, quickly fall behind GROUP BY aggregation in performance when the cardinality increases. It is one of those cases when one should always test on live size data sets.
    😎

  • Jeff Moden

    SSC Guru

    Points: 996645

    Eirikur Eiriksson - Sunday, September 16, 2018 1:12 AM

    The Window(ing) functions perform well when applied to sets of relatively moderate cardinality, quickly fall behind GROUP BY aggregation in performance when the cardinality increases. It is one of those cases when one should always test on live size data sets.
    😎

    Not to mention the problem with needing to use a DISTINCT on a GROUP BY. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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