August 21, 2018 at 1:43 am
Hi guys, just a general question.
I'm writing a query in order to create quite a large report. I have multiple conditions across a few tables but I also have to make multiple selects from the same table but with different conditions. So I've got a SELECT from TableA where Status=1, that's one set. Then I need a SELECT FROM TableA Where Status=2, that's another set etc.
I've found myself to be creating LEFT OUTER JOINs onto each set I need. I'm about a quarter into this query and I've got 6 Joins already.
So my questions are:
1. Should I avoid many Joins and seek to find other ways?
2. How many Joins are considered too many?
3. Is it best to make temp sets using temp tables and joining onto those or is best to LEFT OUTER JOIN(Temp Set Here)?
I won't be able to upload any code so if it's OK I'd like to skip that. My questions are really just general knowledge and rules of thumb on using Joins where I should be, compared to losing control with them.
Thanks
August 21, 2018 at 1:55 am
This is going to be difficult without the SQL. First step, can you post that? 6 joins isn't really a lot, in my view; i use plenty of queries that use more than that.
On your earlier statement, why are you not using SELECT {YourColumns} FROM TableA WHERE status IN (1,2);? It seems you're saying you currently have SELECT {YourColumns} FROM TableA WHERE status = 1 UNION SELECT {YourColumns} FROM TableA WHERE status = 2;
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 2:17 am
Thom A - Tuesday, August 21, 2018 1:55 AMThis is going to be difficult without the SQL. First step, can you post that? 6 joins isn't really a lot, in my view; i use plenty of queries that use more than that.On your earlier statement, why are you not using SELECT {YourColumns} FROM TableA WHERE status IN (1,2);? It seems you're saying you currently have SELECT {YourColumns} FROM TableA WHERE status = 1 UNION SELECT {YourColumns} FROM TableA WHERE status = 2;
Got a chance to get the code out. Check it out.
--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 (
SELECT ParameterName, ParameterId, ParameterValue
FROM Company.Schema.GeneralParameter
WHERE ParameterName='ClaimType'
)GenPar
LEFT OUTER JOIN Company.Schema.ClaimMain ClaimMain
ON GenPar.ParameterId = ClaimMain.ClaimType
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
LEFT OUTER JOIN (
SELECT *
FROM Company.Schema.ClaimMain
WHERE CurrentStatus=12
)Pending
ON Pending.ClaimNumber = ClaimMain.ClaimNumber
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
How bad is it? This table will also have 37 columns. I'm new to all this but wouldn't 37 columns be hard to read? User requirements hey.
😐
August 21, 2018 at 2:23 am
NikosV - Tuesday, August 21, 2018 2:17 AMGot a chance to get the code out. Check it out.
How bad is it?
😐
Oh, that's a bit of a mess. 🙂 Definitely no need for all those extra joins, and those subqueries make it a lot harder to understand. give me a while and I'll show you how I would have done it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 2:34 am
Thom A - Tuesday, August 21, 2018 2:23 AMNikosV - Tuesday, August 21, 2018 2:17 AMGot a chance to get the code out. Check it out.
How bad is it?
😐
Oh, that's a bit of a mess. 🙂 Definitely no need for all those extra joins, and those subqueries make it a lot harder to understand. give me a while and I'll show you how I would have done it.
Thanks.
August 21, 2018 at 2:40 am
Ok, I'm pretty sure that this gets the same result. If it doesn't have a look at what I've done and see if you can understand the method. The number of joins you had previously were way over the top (you JOINed to the ClaimMain table 8 times!). For things like this, use a CASE expression. There's no need to create a JOIN for each different type of Status. There latter will likely be awful for query performance. Also, avoid using Subqueries in your JOINs like that. It makes things far far harder to read, and you can't reference object outside the subquery when you do that. If you have to use a subquery, use a CROSS or OUTER APPLY, as then you can reference external objects.
Anyway, see how this works, and if it doesn't, please let me know. (Note you might need to add a WHERE clause of WHERE CM.CurrentStatus IN (10,15,25,12) :
Cheers.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 2:59 am
Thom A - Tuesday, August 21, 2018 2:40 AMOk, I'm pretty sure that this gets the same result. If it doesn't have a look at what I've done and see if you can understand the method. The number of joins you had previously were way over the top (you JOINed to the ClaimMain table 8 times!). For things like this, use a CASE expression. There's no need to create a JOIN for each different type of Status. There latter will likely be awful for query performance. Also, avoid using Subqueries in your JOINs like that. It makes things far far harder to read, and you can't reference object outside the subquery when you do that. If you have to use a subquery, use a CROSS or OUTER APPLY, as then you can reference external objects.Anyway, see how this works, and if it doesn't, please let me know. (Note you might need to add a WHERE clause of WHERE CM.CurrentStatus IN (10,15,25,12) :
DECLARE @DateFrom varchar = '2018-01-01';DECLARE @DateTo varchar = '2018-08-21';SELECT GP.ParameterValue AS ClaimType, --I try to avoid using special characters in object names, including whitespaceCOUNT(CASE CM.CurrentStatus WHEN 10 THEN 1 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 PendingSIFROM Company.[Schema].GeneralParameter GPLEFT JOIN Company.[Schema].ClaimMain CM ON GenPar.ParameterId = ClaimMain.ClaimTypeLEFT JOIN Company.[Schema].PolicyCover PC ON PC.PolicyNumber = CM.PolicyNumberWHERE CM.OpeningRegistrationDate BETWEEN @DateFrom AND @DateToGROUP BY GP.ParameterValue;Cheers.
Getting this not sure why...
Msg 241, Level 16, State 1, Line 4
Conversion failed when converting date and/or time from character string.
August 21, 2018 at 3:06 am
I hadn't changed your definition of your variables. It might be that your login in interpreting the second date as 20182108, which doesn't exist. Try using:
If that doesn't work, is CM.OpeningRegistrationDate being stored as a varchar?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 3:30 am
Thom A - Tuesday, August 21, 2018 3:06 AMI hadn't changed your definition of your variables. It might be that your login in interpreting the second date as 20182108, which doesn't exist. Try using:DECLARE @DateFrom varchar = '20180101';DECLARE @DateTo varchar = '20180821';If that doesn't work, is CM.OpeningRegistrationDate being stored as a varchar?
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.
August 21, 2018 at 3:39 am
NikosV - Tuesday, August 21, 2018 3:30 AMOK 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?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 4:01 am
Thom A - Tuesday, August 21, 2018 3:39 AMNikosV - Tuesday, August 21, 2018 3:30 AMOK 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.
August 21, 2018 at 5:02 am
How did I not notice this before... I (foolishly) assumed you declared your variables as a date, you hadn't, they were a varchar. Use the appropriate datatype for your data. If you have a date, store it as a date:
Also, if you are declaring, converting, a varchar then always declare your length. For example, instead of just varchar, use varchar(10) (obviously use a length appropriate for your data).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 12:37 pm
SUM(CASE WHEN CM.CurrentStatus = 10 THEN 1 ELSE 0 END) AS SubmittedClaims,
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 21, 2018 at 12:58 pm
Jeffrey Williams 3188 - Tuesday, August 21, 2018 12:37 PMThom - shouldn't the COUNT actually be a SUM as in:SUM(CASE WHEN CM.CurrentStatus = 10 THEN 1 ELSE 0 END) AS SubmittedClaims,
They would result in the same answer. Aggregate functions automatically ignore NULL values. A CASE expression without an ELSE will return NULL, so won't be included in the COUNT. For example:
Notice that the value for Counting and Summing is 3. This is probably more down to preference that anything. Personally, I prefer COUNT in scenarios like this, as (at the end of the day) the column is counting something, not summing.
Yes SET ANSI_NULLS OFF would "break" the COUNT, but that poses the question "Why are you using SET ANSI_NULLS OFF?" . It's not "standard" behaviour and the feature is deprecated: SET ANSI_NULLS (Transact-SQL).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 21, 2018 at 2:00 pm
Thom A - Tuesday, August 21, 2018 12:58 PMJeffrey Williams 3188 - Tuesday, August 21, 2018 12:37 PMThom - shouldn't the COUNT actually be a SUM as in:SUM(CASE WHEN CM.CurrentStatus = 10 THEN 1 ELSE 0 END) AS SubmittedClaims,
They would result in the same answer. Aggregate functions automatically ignore NULL values. A CASE expression without an ELSE will return NULL, so won't be included in the COUNT. For example:
USE Sandbox;GOCREATE TABLE T (S char(1));INSERT INTO T(S)VALUES('a'),('b'),('c'),('a'),('d'),('a'),('c');SELECT COUNT(CASE S WHEN 'a' THEN 1 END) AS Counting,SUM(CASE S WHEN 'a' THEN 1 ELSE 0 END) AS SummingFROM T;DROP TABLE T;Notice that the value for Counting and Summing is 3. This is probably more down to preference that anything. Personally, I prefer COUNT in scenarios like this, as (at the end of the day) the column is counting something, not summing.
Yes SET ANSI_NULLS OFF would "break" the COUNT, but that poses the question "Why are you using SET ANSI_NULLS OFF?" . It's not "standard" behaviour and the feature is deprecated: SET ANSI_NULLS (Transact-SQL).
Right - now I recall why I choose SUM over COUNT - just preference and I got so used to using it I actually forgot why 😉 I prefer using SUM because I don't like the warnings issued when NULL values are eliminated from the results. As for SET ANSI_NULLS OFF - that is something I would never set for the same reason - it isn't standard and it can cause unforeseen issues - as well as SET ANSI_WARNINGS OFF which would eliminate the warnings but also has side effects.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply