Multiple Joins

Question

Multiple Joins

Viewing 15 posts - 16 through 30 (of 34 total)

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

andycadley SSCertifiable Points: 5296 More actions · Answer 1

I too would tend to use the CASE variant, but it's worth noting that:

LEFT OUTER JOIN ( SELECT * FROM Company.Schema.ClaimMain WHERE CurrentStatus=10 )Submitted ON Submitted.ClaimNumber = ClaimMain.ClaimNumber

LEFT OUTER JOIN ( SELECT * FROM Company.Schema.ClaimMain WHERE CurrentStatus=15 )ApprovalProvision ON ApprovalProvision.ClaimNumber = ClaimMain.ClaimNumber

LEFT OUTER JOIN (
      SELECT *
      FROM Company.Schema.ClaimMain
      WHERE CurrentStatus=25
     )Declined
ON Declined.ClaimNumber = ClaimMain.ClaimNumber

Could more succinctly be written as:

LEFT OUTER JOIN Company.Schema.ClaimMain As Submitted ON Submitted.ClaimNumber = ClaimMain.ClaimNumber AND Submitted.CurrentStatus=10

LEFT OUTER JOIN Company.Schema.ClaimMain As ApprovalProvision
ON ApprovalProvision.ClaimNumber = ClaimMain.ClaimNumber
AND ApprovalProvision.CurrentStatus=15

LEFT OUTER JOIN Company.Schema.ClaimMain As Declined
ON Declined.ClaimNumber = ClaimMain.ClaimNumber
AND Declined.CurrentStatus=25

NikosV SSCrazy Points: 2111 More actions · Answer 2

NikosV - Tuesday, August 21, 2018 4:01 AM
Thom A - Tuesday, August 21, 2018 3:39 AM
NikosV - Tuesday, August 21, 2018 3:30 AM
OK I sorted the date thing.
Thing is, in my first count, I want to count how many occurences of ClaimNumber I have, not how many occurences of CurrentStatus 10. I need to say get me the count of all ClaimNumbers that have a CurrentStatus of 10, get me the count of all ClaimNumbers that have a CurrentStatus of 15 and so on...
Your code is so much tidier and nicer but the results are coming back wrong whereas with my initial code salad the results look like they're correct.
Any suggestions? I do get the gist of what you're saying though.
Without sample data, not really. Can you provide Sample data dn expected results? See the link in my signature.
I can't, however, see anywhere in your original code where you would do a total count of the claim number. Your COUNT is COUNT(Submitted.ClaimNumber) AS 'Submitted Claims', which will only count claims WHERE CurrentStatus=10, as that's the criteria in the JOIN. If you do need a COUNT of all claims, why not add a COUNT(CM.{ID COLUMN}) to your SELECT?
OK, I'll check it out thanks.

Hi Thom. Yes that's what I meant. I am counting the amount of claims with a current status of 10. When I run your code I get wrong results back. For example for claims of type Cancellation I'm getting 16 back when I should be getting two. The current code returns two.
Tell me what you mean exactly when you guys say sample data so I can sort it out and make it easier for you.
Thanks again.

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

NikosV - Wednesday, August 22, 2018 7:46 AM
Hi Thom. Yes that's what I meant. I am counting the amount of claims with a current status of 10. When I run your code I get wrong results back. For example for claims of type Cancellation I'm getting 16 back when I should be getting two. The current code returns two.
Tell me what you mean exactly when you guys say sample data so I can sort it out and make it easier for you.
Thanks again.

Have a look at the link in my signature. 🙂 I suspect (this is a total guess mind) that you'll need to use a COUNT(DISTINCT..) instead. Maybe something like:

COUNT(DISTINCT CASE CM.CurrentStatus WHEN 10 THEN CM.ClaimID END) AS SubmittedClaims,

(The column name in the CASE is a total guess.)

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

Thom A - Wednesday, August 22, 2018 7:53 AM
NikosV - Wednesday, August 22, 2018 7:46 AM
Hi Thom. Yes that's what I meant. I am counting the amount of claims with a current status of 10. When I run your code I get wrong results back. For example for claims of type Cancellation I'm getting 16 back when I should be getting two. The current code returns two.
Tell me what you mean exactly when you guys say sample data so I can sort it out and make it easier for you.
Thanks again.
Have a look at the link in my signature. 🙂 I suspect (this is a total guess mind) that you'll need to use a COUNT(DISTINCT..) instead. Maybe something like:
COUNT(DISTINCT CASE CM.CurrentStatus WHEN 10 THEN CM.ClaimID END) AS SubmittedClaims,
(The column name in the CASE is a total guess.)

OK I'll get back to you tomorrow when I go into the office. Thanks again.

NikosV SSCrazy Points: 2111 More actions · Answer 5

Hi Thom.

I've changed the code to this.

DECLARE @DateFrom DATE = '2018-01-01'; DECLARE @DateTo DATE = '2018-08-21';

SELECT GenPar.ParameterValue AS ClaimType,
       COUNT(CASE ClaimMain.CurrentStatus WHEN 10 THEN 1 END) AS SubmittedClaims,
       COUNT(CASE ClaimMain.CurrentStatus WHEN 15 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,
       SUM(CASE ClaimMain.CurrentStatus WHEN 10 THEN PC.SumAssured END) AS SubmittedSO,
       SUM(CASE ClaimMain.CurrentStatus WHEN 15 THEN PC.SumAssured END) AS ApprovedSI,
       SUM(CASE ClaimMain.CurrentStatus WHEN 25 THEN PC.SumAssured END) AS DeclinedSI,
       SUM(CASE ClaimMain.CurrentStatus WHEN 12 THEN PC.SumAssured END) AS PendingSI
FROM
(
  SELECT *
  FROM Company.Schema.GeneralParameter
  WHERE ParameterName = 'ClaimType'
)GenPar
     LEFT JOIN Company.Schema.ClaimMain ClaimMain ON GenPar.ParameterId = ClaimMain.ClaimType
     LEFT JOIN Company.Schema.PolicyCover PC ON PC.PolicyNumber = ClaimMain.PolicyNumber
WHERE ClaimMain.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo
GROUP BY GenPar.ParameterValue;

I had to narrow down what GeneralParameter was returning. All is OK now but I'm having the same prob with before.
An example, I have a Claim Type X for which I have two records, the suggested code is returning 72 records. A DISTINCT CASE returns 1 record. The initially posted code returns the correct count.

The way I was thinking was create virtual tables and join onto them, now the initial query runs in 0 seconds so it's not that it's delaying but I think you're right it looks like a mess so I defo prefer your approach. Just not sure why this is happening. After all, I believe your code is communicating it's intentions clearly.

EDIT
Just a small change. This line of code:

COUNT(CASE ClaimMain.CurrentStatus WHEN 15 THEN 1 END) AS ApprovedClaims

Needs to say When ClaimMain.CurrentStatus is 40 OR 30 then....whatever. So the 15 is wrong and needs to be replaced by 40 OR 30.
Sorry about that.

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

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;

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 7

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?

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

Really need to have sample data and expected result now. And, if you have 2 status it can be, have you considered using IN in the CASE expression?

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 9

Claim Main Table Sample

CREATE TABLE [Schema.ClaimMain] ( [PolicyNumber] [int] NULL, [ClaimNumber] [int] NOT NULL, [ClaimType] [smallint] NOT NULL, [CurrentStatus] [smallint] NOT NULL, [OpeningRegistrationDate] [datetime] NOT NULL, [ClosingRegistrationDate] [datetime] NOT NULL, CONSTRAINT [PK ClaimMain.{PClaimMainPK}] PRIMARY KEY CLUSTERED ( [PClaimMainPK] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

PolicyCover Table Sample
CREATE TABLE [Schema].[PolicyCover] ( [PolicyNumber] [int] NULL, [LayerNumber] [smallint] NULL, [SumAssured] [decimal](18, 6) NULL, [PCoversPk] [int] NOT NULL, CONSTRAINT [PK PolicyCover.{PCoversPk}] PRIMARY KEY CLUSTERED ( [PCoversPk] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

General Parameter Table Sample
CREATE TABLE [Schema].[GeneralParameter] ( [Id] [int] IDENTITY(1,1) NOT NULL, [ParameterName] [varchar](30) NOT NULL, [ParameterId] [int] NOT NULL, [ParameterValue] [varchar](255) NOT NULL CONSTRAINT [PK GeneralParameter.Id] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

Expected results in file.

Current Statuses From ClaimMain
10 = Submitted
30 AND 40 = Approved
25 = Declined
12 = Pending

PolicyCover.SumAssured must not be 0 and the PolicyCover.LayerNumber must be 0.
All returned records must be between @DateFrom and @DateTo
The parameter name in GeneralParameter must be only 'ClaimType'
The parameter values to be taken into consideration are in the IN condition of the last posted code.

If you need anything else let me know.
Thanks

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

I can't see any sample data in your SQL, only DDL definitions. I'll need sample data and expected results too please (note that many users, including myself, are unhappy to open an Excel document from a stranger).

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 11

Thom A - Thursday, August 23, 2018 3:05 AM
I can't see any sample data in your SQL, only DDL definitions. I'll need sample data and expected results too please (note that many users, including myself, are unhappy to open an Excel document from a stranger).

OK. Understood. How should the sample data (I imagine you mean records) be displayed to you? I've never done this before. The expected results can be shown in a code window I suppose. How does all this go?

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

NikosV - Thursday, August 23, 2018 3:42 AM
OK. Understood. How should the sample data (I imagine you mean records) be displayed to you? I've never done this before. The expected results can be shown in a code window I suppose. How does all this go?

It's all in the signature. 🙂

Thom~

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

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

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.

NikosV SSCrazy Points: 2111 More actions · Answer 14

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.

NikosV SSCrazy Points: 2111 More actions · Answer 15

Quick question, what does CASE WHEN THEN 1 mean? What's the THEN 1 doing exactly?