Best way to join onto same table to extract different data

  • I'm the original poster of this question https://www.sqlservercentral.com/Forums/1990728/Multiple-Joins and during the conversation I was told my existing code was bad because it joined onto the same table 8 times. This makes sense to me so I am providing some sample data so you guys can tell me your opinions.

    I have a table called ClaimMain and this table has many columns but for examples sake I will only mention the columns I'm interested in. So ClaimMain looks like this.


    ClaimNumber    CurrentStatus
    12345                 10
    12233                 12
    112233                15
    1155332               20

    There are multiple ClaimNumber occurences with the same CurrentStatus. So what I want is a COUNT of each ClaimNumber with X CurrentStatus.
    This is my code so far.


    --Report 1B - Life & Supplimentary Benefits Status Summary Report

    DECLARE @DateFrom VARCHAR = '2018-01-01'
    DECLARE @DateTo VARCHAR = '2018-08-21'

    SELECT
    GenPar.ParameterValue AS 'Claim Type',
    COUNT(Submitted.ClaimNumber) AS 'Submitted Claims',
    COUNT(ApprovalProvision.ClaimNumber) AS 'Approved Claims',
    COUNT(Declined.ClaimNumber) AS 'Declined Claims',
    COUNT(Pending.ClaimNumber) AS 'Pending Claims',
    ISNULL(SUM(SubmittedSum.[Sum Insured]),0) AS 'Total Submitted Sum Insured',
    ISNULL(SUM(ApprovedSum.[Sum Insured]),0) AS 'Total Approved Sum Insured',
    ISNULL(SUM(RejectedSum.[Sum Insured]),0) AS 'Total Rejected Sum Insured',
    ISNULL(SUM(PendingSum.[Sum Insured]),0) AS 'Total Pending Sum Insured'
    FROM ( --General Parameter
    SELECT ParameterName, ParameterId, ParameterValue
    FROM Company.Schema.GeneralParameter
    WHERE ParameterName='ClaimType'
    )GenPar
    --Claim Main
    LEFT OUTER JOIN Company.Schema.ClaimMain ClaimMain
    ON GenPar.ParameterId = ClaimMain.ClaimType

    --Get Status 10
    LEFT OUTER JOIN (
      SELECT *
      FROM Company.Schema.ClaimMain
      WHERE CurrentStatus=10
      )Submitted
    ON Submitted.ClaimNumber = ClaimMain.ClaimNumber
    --Get Status 15
    LEFT OUTER JOIN (
      SELECT *
      FROM Company.Schema.ClaimMain
      WHERE CurrentStatus=15
      )ApprovalProvision
    ON ApprovalProvision.ClaimNumber = ClaimMain.ClaimNumber
    --Get Status 25
    LEFT OUTER JOIN (
      SELECT *
      FROM Company.Schema.ClaimMain
      WHERE CurrentStatus=25
      )Declined
    ON Declined.ClaimNumber = ClaimMain.ClaimNumber
    --get Status 12
    LEFT OUTER JOIN (
      SELECT *
      FROM Company.Schema.ClaimMain
      WHERE CurrentStatus=12
      )Pending
    ON Pending.ClaimNumber = ClaimMain.ClaimNumber

    --Now get Sums for each Status
    LEFT OUTER JOIN (
      SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
      FROM Company.Schema.PolicyCover PCover
      INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
        ON CMInner.PolicyNumber = PCover.PolicyNumber

      WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
         AND CMInner.CurrentStatus=10
      GROUP BY PCover.PolicyNumber
      )SubmittedSum
    ON SubmittedSum.PolicyNumber = ClaimMain.PolicyNumber

    LEFT OUTER JOIN (
      SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
      FROM Company.Schema.PolicyCover PCover
      INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
        ON CMInner.PolicyNumber = PCover.PolicyNumber
      WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
         AND CMInner.CurrentStatus=15
      GROUP BY PCover.PolicyNumber
      )ApprovedSum
    ON ApprovedSum.PolicyNumber = ClaimMain.PolicyNumber

    LEFT OUTER JOIN (
      SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
      FROM Company.Schema.PolicyCover PCover
      INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
        ON CMInner.PolicyNumber = PCover.PolicyNumber

      WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
         AND CMInner.CurrentStatus=25
      GROUP BY PCover.PolicyNumber
      )RejectedSum
    ON RejectedSum.PolicyNumber = ClaimMain.PolicyNumber

    LEFT OUTER JOIN (
      SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
      FROM Company.Schema.PolicyCover PCover
      INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
        ON CMInner.PolicyNumber = PCover.PolicyNumber

      WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
         AND CMInner.CurrentStatus=12
      GROUP BY PCover.PolicyNumber
      )PendingSum
    ON PendingSum.PolicyNumber = ClaimMain.PolicyNumber

    WHERE ClaimMain.OpeningRegistrationDate BETWEEN '2018-01-01' AND '2018-08-20'

    GROUP BY GenPar.ParameterValue

    The above code returns the correct results but as you can see I am joining multiple times onto the same table and the reason being that I want a COUNT of ClaimNumbers that have been Submitted. Again, I want a COUNT of ClaimNumbers that have been Approved, the COUNT of ClaimNumbers that have been declined etc. 

    I want to avoid joining too many times onto the same table. Basically what I'm doing is making temporary little sets with exactly the data I need and joining onto each set.

    I do this quite often actually, joining onto virtual tables. I haven't had any real problems to be honest but I do want to be aware of unneeded repetitive code I may do as a junior so I don't get used to doing it.

    Any suggestions on how to make my code better?

    Thanks in advance.

  • This still doesn't help.  We don't have any information about Company.Schema.GeneralParameter, Company.Schema.ClaimMain,  Company.Schema.PolicyCover or any other table.  Stopped looking after a while.  I'm not going to try to create the table structures and data for these.  I looked at some of the query and saw that things like this could be combined.

    LEFT OUTER JOIN (
    SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
    FROM Company.Schema.PolicyCover PCover
    INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
      ON CMInner.PolicyNumber = PCover.PolicyNumber

    WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
      AND CMInner.CurrentStatus=25
    GROUP BY PCover.PolicyNumber
     )RejectedSum
    ON RejectedSum.PolicyNumber = ClaimMain.PolicyNumber

    LEFT OUTER JOIN (
    SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
    FROM Company.Schema.PolicyCover PCover
    INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
      ON CMInner.PolicyNumber = PCover.PolicyNumber

    WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
      AND CMInner.CurrentStatus=12
    GROUP BY PCover.PolicyNumber
     )PendingSum

    Combine this to.  I'm sure there is more as well

    SELECT PCover.PolicyNumber,
            SUM(case when CMInner.CurrentStatus=25 then SumAssured else 0 end) AS 'RejectedSum'
            SUM(case when CMInner.CurrentStatus=12 then SumAssured else 0 end) AS 'PendingSum'
    FROM Company.Schema.PolicyCover PCover
    INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
      ON CMInner.PolicyNumber = PCover.PolicyNumber

    WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
      --AND CMInner.CurrentStatus=25
    GROUP BY PCover.PolicyNumber

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Please don't start multiple threads for essentially the same question.  You were already told in the other thread how to rewrite this.  If you are still having issues with it, then you should post in the original thread rather than creating a new thread.  This helps keep the thread coherent.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Tuesday, August 21, 2018 9:41 AM

    Please don't start multiple threads for essentially the same question.  You were already told in the other thread how to rewrite this.  If you are still having issues with it, then you should post in the original thread rather than creating a new thread.  This helps keep the thread coherent.

    Drew

    Ok thanks for your time.

  • Mike01 - Tuesday, August 21, 2018 9:37 AM

    This still doesn't help.  We don't have any information about Company.Schema.GeneralParameter, Company.Schema.ClaimMain,  Company.Schema.PolicyCover or any other table.  Stopped looking after a while.  I'm not going to try to create the table structures and data for these.  I looked at some of the query and saw that things like this could be combined.

    LEFT OUTER JOIN (
    SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
    FROM Company.Schema.PolicyCover PCover
    INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
      ON CMInner.PolicyNumber = PCover.PolicyNumber

    WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
      AND CMInner.CurrentStatus=25
    GROUP BY PCover.PolicyNumber
     )RejectedSum
    ON RejectedSum.PolicyNumber = ClaimMain.PolicyNumber

    LEFT OUTER JOIN (
    SELECT PCover.PolicyNumber, SUM(SumAssured) AS 'Sum Insured'
    FROM Company.Schema.PolicyCover PCover
    INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
      ON CMInner.PolicyNumber = PCover.PolicyNumber

    WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
      AND CMInner.CurrentStatus=12
    GROUP BY PCover.PolicyNumber
     )PendingSum

    Combine this to.  I'm sure there is more as well

    SELECT PCover.PolicyNumber,
            SUM(case when CMInner.CurrentStatus=25 then SumAssured else 0 end) AS 'RejectedSum'
            SUM(case when CMInner.CurrentStatus=12 then SumAssured else 0 end) AS 'PendingSum'
    FROM Company.Schema.PolicyCover PCover
    INNER JOIN (SELECT PolicyNumber,CurrentStatus FROM Company.Schema.ClaimMain)CMInner
      ON CMInner.PolicyNumber = PCover.PolicyNumber

    WHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
      --AND CMInner.CurrentStatus=25
    GROUP BY PCover.PolicyNumber

    Thanks for your time.

Viewing 5 posts - 1 through 4 (of 4 total)

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