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 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply