Post reply

Selecting Min of multiple values, including an operation

  • September 7, 2018 at 10:31 am

    #362728

    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

  • September 7, 2018 at 10:49 am

    #2004921

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • September 7, 2018 at 10:59 am

    #2004925

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

  • September 8, 2018 at 5:29 pm

    #2004980

    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

  • September 10, 2018 at 7:50 am

    #2005068

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

  • September 15, 2018 at 10:16 pm

    #2005880

    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

  • September 16, 2018 at 1:12 am

    #2005881

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

  • September 16, 2018 at 11:14 am

    #2005897

    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.

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

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

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