August 21, 2018 at 9:21 am
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.
August 21, 2018 at 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
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/
August 21, 2018 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 21, 2018 at 10:12 am
drew.allen - Tuesday, August 21, 2018 9:41 AMPlease 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.
August 21, 2018 at 10:13 am
Mike01 - Tuesday, August 21, 2018 9:37 AMThis 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.PolicyNumberWHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
AND CMInner.CurrentStatus=25
GROUP BY PCover.PolicyNumber
)RejectedSum
ON RejectedSum.PolicyNumber = ClaimMain.PolicyNumberLEFT 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.PolicyNumberWHERE PCover.SumAssured <> 0 AND PCover.LayerNumber = 0
AND CMInner.CurrentStatus=12
GROUP BY PCover.PolicyNumber
)PendingSumCombine 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.PolicyNumberWHERE 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