Multiple Joins

Question

Multiple Joins

Viewing 5 posts - 31 through 34 (of 34 total)

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

Thom A SSC Guru Points: 99128 More actions · Answer 1

NikosV - Thursday, August 23, 2018 11:28 PM
Quick question, what does CASE WHEN THEN 1 mean? What's the THEN 1 doing exactly?

It's simply returning a non-NULL value.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NikosV SSCrazy Points: 2111 More actions · Answer 2

Thom A - Friday, August 24, 2018 1:34 AM
NikosV - Thursday, August 23, 2018 11:28 PM
Quick question, what does CASE WHEN THEN 1 mean? What's the THEN 1 doing exactly?
It's simply returning a non-NULL value.

OK. When I say COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) AS Submitted
I mean to say this: Count every record with a CurrentStatus of 10 and show me the count. If it's zero, show me zero.

So if I have 4 records with case 10 should this syntax return the correct count or will it show me 1?

Thom A SSC Guru Points: 99128 More actions · Answer 3

NikosV - Friday, August 24, 2018 1:49 AM
OK. When I say COUNT(CASE CurrentStatus WHEN 10 THEN 1 END) AS Submitted
I mean to say this: Count every record with a CurrentStatus of 10 and show me the count. If it's zero, show me zero.
So if I have 4 records with case 10 should this syntax return the correct count or will it show me 1?

Perhaps better displayed with some sample data:

USE Sandbox;
CREATE TABLE dbo.TestTable (CurrentStatus int);
INSERT INTO dbo.TestTable (CurrentStatus)
VALUES(10),(20),(30),(40),(10),(10),(50),(40),(20),(10),(20);
--4 10s
--3 20s
--1 30
--2 40s
--1 50
SELECT COUNT(CASE CurrentStatus WHEN 10 THEN 1 END), --Returns 4
   COUNT(CASE CurrentStatus WHEN 20 THEN 1 END), --Returns 3
    COUNT(CASE CurrentStatus WHEN 30 THEN 1 END), --Returns 1
    COUNT(CASE CurrentStatus WHEN 40 THEN 1 END), --Returns 2
    COUNT(CASE CurrentStatus WHEN 50 THEN 1 END) --Returns 1
FROM dbo.TestTable;
DROP TABLE dbo.TestTable;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

NikosV SSCrazy Points: 2111 More actions · Answer 4

NikosV - Thursday, August 23, 2018 7:56 AM
Jonathan AC Roberts - Thursday, August 23, 2018 5:06 AM
NikosV - Thursday, August 23, 2018 2:34 AM
Thom A - Thursday, August 23, 2018 1:56 AM
YOu really need to get away from using a subquery for EVERY expression (I.e. a WHERE, a JOIN, etc it's awful practice and makes your SQL very difficult to read. Just add another clause to your WHERE. It can have more than one:
DECLARE @DateFrom date = '20180101';
DECLARE @DateTo date = '20180821';
SELECT GP.ParameterValue AS ClaimType, --I try to avoid using special characters in object names, including whitespace
   COUNT(DISTINCT CASE CM.CurrentStatus WHEN 10 THEN CM.ClaimID END) AS SubmittedClaims,
   COUNT(CASE CM.CurrentStatus WHEN 15 THEN 1 END) AS ApprovedClaims,
   COUNT(CASE CM.CurrentStatus WHEN 25 THEN 1 END) AS DeclinedClaims,
   COUNT(CASE CM.CurrentStatus WHEN 12 THEN 1 END) AS PendingClaims,
   SUM(CASE CM.CurrentStatus WHEN 10 THEN PC.[Sum Insured] END) AS SubmittedSO,
   SUM(CASE CM.CurrentStatus WHEN 15 THEN PC.[Sum Insured] END) AS ApprovedSI,
   SUM(CASE CM.CurrentStatus WHEN 25 THEN PC.[Sum Insured] END) AS DeclinedSI,
   SUM(CASE CM.CurrentStatus WHEN 12 THEN PC.[Sum Insured] END) AS PendingSI
FROM Company.[Schema].GeneralParameter GP
LEFT JOIN Company.[Schema].ClaimMain CM ON GenPar.ParameterId = ClaimMain.ClaimType
LEFT JOIN Company.[Schema].PolicyCover PC ON PC.PolicyNumber = CM.PolicyNumber
WHERE CM.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
AND GP.ParameterName = 'ClaimType' --Your other WHERE clause
GROUP BY GP.ParameterValue;
Thanks Thom but the result is still coming back wrong. Here's your code altered with all the necessary conditions. The initial code returns 5 records (this is correct) whereas the suggested code (below) returns 2.
DECLARE @DateFrom DATE = '2018-01-01'; DECLARE @DateTo DATE = '2018-08-21';
SELECT GenPar.ParameterValue AS ClaimType,
       COUNT(CASE ClaimMain.CurrentStatus WHEN 10 THEN ClaimMain.ClaimNumber END) AS SubmittedClaims,
       COUNT(CASE ClaimMain.CurrentStatus WHEN 40 THEN 1 END) AS ApprovedClaims,
       COUNT(CASE ClaimMain.CurrentStatus WHEN 25 THEN 1 END) AS DeclinedClaims,
       COUNT(CASE ClaimMain.CurrentStatus WHEN 12 THEN 1 END) AS PendingClaims,
       ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PCover.SumAssured END),0) AS TotalSubmittedSumInsured,
       ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 40 THEN PCover.SumAssured END),0) AS TotalApprovedSumInsured,
       ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PCover.SumAssured END),0) AS TotalRejectedSumInsured,
       ISNULL(SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PCover.SumAssured END),0) AS TotalPendingSumInsured
FROM Company.Schema.GeneralParameter GenPar
     LEFT JOIN Company.Schema.ClaimMain ClaimMain ON GenPar.ParameterId = ClaimMain.ClaimType
     LEFT JOIN Company.Schema.PolicyCover PCover ON PCover.PolicyNumber = ClaimMain.PolicyNumber
WHERE
ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
AND GenPar.ParameterName = 'ClaimType'
AND PCover.LayerNumber=0
AND PCover.SumAssured <> 0
AND GenPar.ParameterValue IN ('Resiliation due to Death','Death (Old)','Death (annuity)','Death','Accidental Death (Common Carrier)',
        'Death with Accidental Death','Refund','Accidental Death Top-Up','Accidental Death (Common Carrier) Top-Up',
        'Income Cover','Co-insurance - First Death','Total & Permanent Disability','Accidental Total & Permanent Disability',
        'Disability due to Disease','Disability due to mental Health','Accidental Death ( Traffic )',
        'Disability due to Accident ( Traffic )','Disability without Benefit','First Death','First Death - Accidental',
        'Partial Disability','Total Disability','Physiological Disability >= 25%','Physiological Disability >= 67%',
        'Economical Disability >= 25%','Economical Disability >= 67%','Economical Disability >= 25% due to Physiological Disability >= 25%',
        'Economical Disability >= 67% due to Physiological Disability >= 25%','Accidental Economical Disability >= 67% due to Physiological Disability >= 25%',
        'Accidental Physiological Disability >= 67%','Disability','Waiver of Premiums','Waiver of Premium',
        'Non Life Payee Death','Main Annuitant Death with Living Spouse','2nd Annuitant Death - Living Spouse',
        'Spouse Rider Death','Total & Permanent Disability','Disability WP 565','Payor Death - WOP','Payor Disability - WOP',
        'Medical Expense Claim','Medical Investigation Expense','Waiver Benefit','LTC Event Claim',
        'LTC Death','Critical Illness','LifeCare Critical Illness','Death Basic Cover','Accidental Death',
        'Family Protector','Accidental Death with PTD','Accidental Death with PTD-DO NOT USE',
        'Hospital Indemnity','Income Replacement','Accidental Permanent Loss','Death Other Than Accident (CV Only)',
        'Accidental Death Basic Cover','Accidental Death Travel')
GROUP BY GenPar.ParameterValue
ORDER BY GenPar.ParameterValue;
Also, this line COUNT(CASE ClaimMain.CurrentStatus WHEN 40 THEN ClaimMain.ClaimNumber END)
needs to take into consideration CurrentStatus 30 as well as 40.
What else do you need for me to help you?
One change I would try when I have a large number of values in an IN operator is to put them in a temporary table:
IF OBJECT_ID('tempdb..#ParameterValues','U') IS NOT NULL DROP TABLE #ParameterValues; CREATE TABLE #ParameterValues ( ParameterValue varchar(255) PRIMARY KEY CLUSTERED ); INSERT INTO #ParameterValues(ParameterValue) SELECT * FROM (VALUES('Resiliation due to Death'),('Death (Old)'),('Death (annuity)'),('Death'),('Accidental Death (Common Carrier)'),(    'Death with Accidental Death'),('Refund'),('Accidental Death Top-Up'),('Accidental Death (Common Carrier) Top-Up'),(    'Income Cover'),('Co-insurance - First Death'),('Total & Permanent Disability'),('Accidental Total & Permanent Disability'),(    'Disability due to Disease'),('Disability due to mental Health'),('Accidental Death ( Traffic )'),(    'Disability due to Accident ( Traffic )'),('Disability without Benefit'),('First Death'),('First Death - Accidental'),(    'Partial Disability'),('Total Disability'),('Physiological Disability >= 25%'),('Physiological Disability >= 67%'),(    'Economical Disability >= 25%'),('Economical Disability >= 67%'),('Economical Disability >= 25% due to Physiological Disability >= 25%'),(    'Economical Disability >= 67% due to Physiological Disability >= 25%'),('Accidental Economical Disability >= 67% due to Physiological Disability >= 25%'),(    'Accidental Physiological Disability >= 67%'),('Disability'),('Waiver of Premiums'),('Waiver of Premium'),(    'Non Life Payee Death'),('Main Annuitant Death with Living Spouse'),('2nd Annuitant Death - Living Spouse'),(    'Spouse Rider Death'),('Total & Permanent Disability'),('Disability WP 565'),('Payor Death - WOP'),('Payor Disability - WOP'),(    'Medical Expense Claim'),('Medical Investigation Expense'),('Waiver Benefit'),('LTC Event Claim'),(    'LTC Death'),('Critical Illness'),('LifeCare Critical Illness'),('Death Basic Cover'),('Accidental Death'),(    'Family Protector'),('Accidental Death with PTD'),('Accidental Death with PTD-DO NOT USE'),(    'Hospital Indemnity'),('Income Replacement'),('Accidental Permanent Loss'),('Death Other Than Accident (CV Only)'),(    'Accidental Death Basic Cover'),('Accidental Death Travel')) T(C)
Then in your query you would only need:
AND GenPar.ParameterValue IN (SELECT ParameterValue FROM #ParameterValues)
(you have more than one 'Total & Permanent Disability' rows in your IN operator)
Also in the WHERE clause you have
FROM Company.Schema.GeneralParameter GenPar LEFT JOIN Company.Schema.ClaimMain ClaimMain ON GenPar.ParameterId = ClaimMain.ClaimType LEFT JOIN Company.Schema.PolicyCover PCover ON PCover.PolicyNumber = ClaimMain.PolicyNumber WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo AND GenPar.ParameterName = 'ClaimType' AND PCover.LayerNumber=0 AND PCover.SumAssured <> 0
Having values other than comparing to NULL from a left joined table in the WHERE clause gets rid of the need for a LEFT JOIN, it is instead an INNER JOIN.
This is a really good idea.
I like the idea of a temp table for many values. Thing is, I'll have to run that code twice because I'll be checking previous year records too. So I do need to tidy the code up urgently. I'll get back tomorrow thanks.

Very good solution and of course performance is much better. Could you tell me for the temp table what T(C) is?

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 5

NikosV - Friday, August 24, 2018 2:13 AM
Very good solution and of course performance is much better. Could you tell me for the temp table what T(C) is?

It's the TableName(ColumnName) from the VALUES
SELECT T.C FROM (VALUES('Resiliation due to Death'),('Death (Old)'),('Death (annuity)'),('Death'),('Accidental Death (Common Carrier)'),( ... 'Accidental Death Basic Cover'),('Accidental Death Travel')) T(C)
I could have put anything, e.g. A(B)