August 14, 2018 at 1:38 am
sgmunson - Monday, August 13, 2018 2:43 PMNikosV - Monday, August 13, 2018 1:55 PMsgmunson - Monday, August 13, 2018 7:54 AMNikosV - Monday, August 13, 2018 6:47 AMLet's say I have Table A and Table B.
Table A can join onto Table B because both tables have a common column. Let's call this column PolicyNumber. However, Table B has multiple records of the same PolicyNumber, this is because each record with X PolicyNumber may differ on other details in Table B so basically each row is distinct.Why is it when I join Table A onto Table B on the PolicyNumber, my overall result comes back wrong?
So what I'm asking is, when I perform a join onto a common column and TableB has multiple occurrences of the same PolicyNumber, will this affect my result and why?
Since I'm joining two common columns, I can't yet see why this ruins my overall result because of the actual values under PolicyNumber.I obviously haven't understood exactly how joins work yet but I have looked up many tutorials and they just explain what each join does. What I need is a clarification on what I asked if possible and also a recommendation on how to approach understanding how joins behave.
ThanksIn simple terms, if Table A has exactly one row for a given Policy Number, and Table B has, say 5 rows, then any of the JOIN types will give you 5 rows, and the values for Table A will be repeated in the result set 5 times, while values from Table B will be the distinct values from it. If Table A instead had 2 rows for the same Policy Number, then the total number or resulting rows would be 10. Thus, if you want a single row result for a given policy number, then you need to be "selective" by including some form of restriction on the JOIN. As an example, let's say that the values in Table B require that you get the most recently dated row, based on the UpdatedDate column. Then you would need to either pre-group the rows by policy number and then take the max UpdatedDate value, and then use that CTE to join to Table A. On the other hand, if you just needed the one row from Table B where a given Status column had some specific value, and you know that only 1 such row for any given policy number can exist with that specific status value, then you could just include an additional condition on the JOIN to Table B that requires the Status column in Table B to have that value. The idea, of course, in both cases, is to restrict the total number of rows to just one for a given policy number. If you use an INNER JOIN, then to get a resulting row, then both tables MUST have a qualifying record. If you use a LEFT OUTER JOIN, then Table B will not need to have a matching value in order for rows from Table A to return values. That's the basics, for the most part, for INNER and OUTER joins...
This is actually a brilliant explanation. I believe I've understood exactly what you're trying to tell me. It makes perfect sense.
Let me just make sure I've got the hang of it because 95% of my work is SQL and it's quite a steep learning curve.I've understood that in cases where I have multiple records of the same value, in order to perform a Join I have to narrow down or as you said, be selective in order to 'force' the system as it were, to bring me one record back. In other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.
Say I say this:
SELECT *
FROM Server.Schema.TableA A
LEFT OUTER JOIN Server.Schema.TableB B
ON A.PolicyNumber = B.PolicyNumberA has one record with PolicyNumber 012345 and B has 5 records of 012345. We said this would return 5 records, but I want one. So I would HAVE TO find a value in TableB like for example:
SELECT *
FROM Server.Schema.TableA A
LEFT OUTER JOIN Server.Schema.TableB B
ON A.PolicyNumber = B.PolicyNumberWHERE B.LayerNumber = 0
GROUP BY A.PolicyNumberOR
SELECT MIN(B.LayerNumber), PolicyNumber
FROM Server.Schema.TableA A
LEFT OUTER JOIN Server.Schema.TableB B
ON A.PolicyNumber = B.PolicyNumber
GROUP BY A.PolicyNumberthat would fetch the PolicyNumber WITH a LayerNumber of say 0, for which Table B has one record only.
Would this approach correct my problem?
The next thing I want to make sure of is this:
When I perform a Join between TableA and TableB on the PolicyNumber, what happens under the hood? Apart from the database engine doing what it normally does, when I say ON A.PolicyNumber = B.PolicyNumber is the systems approach sort of like "OK, I have a PolicyNumber 012345 from A and I'm going to look for PolicyNumber 012345 in B and match them" or does it say, "I've got a column in A called PolicyNumber and I'm going to look for a column in B called PolicyNumber and marry the two columns". In other words, does it have a ByValue behaviour or a ByColumn behaviour?
Just some guidance on the above and I'll give it a jab at work tomorrow. Gawd I really hope my explanations are clear.
Let's first worry about the JOIN condition you specified. You put the Table B column in the WHERE clause, which just turned it into an INNER JOIN... As you had specified a LEFT OUTER JOIN, that indicated you wanted all rows from Table A, and only the matching rows from Table B, based on a Policy Number match, but you're actual matching condition is to have a Layer of 0, so that needs to be part of the JOIN condition as opposed to the WHERE clause. What you would end up with is all rows in Table A and only those with matching rows in B where the layer is 0. You may actually want that, but what you were trying to ask for was all the rows in A, AND bring in the info from Table B only on those rows that have a matching policy number AND a layer of 0. Did that make sense?
It did yes. I'll probably end up posting some code later on. Been at it for two hours can't get my head round it.
Should I edit the initial question or post a new question if I upload code?
August 14, 2018 at 7:51 am
NikosV - Tuesday, August 14, 2018 1:38 AMIt did yes. I'll probably end up posting some code later on. Been at it for two hours can't get my head round it.
Should I edit the initial question or post a new question if I upload code?
Just post the new code as opposed to going back that far and editing a post. Doing that fragments the discussion and forces people reading it to NOT be able to see the sequence of events without moving back and forth and perhaps between pages, which is truly painful. I'm glad that made sense. The key in understanding this element of JOINs is to understand the difference between a JOIN condition and a WHERE condition. JOIN forces the type of JOIN and the conditions for success of only the JOIN. WHERE conditions are imposed on the entire result set, so if you specify a WHERE condition on a column in a LEFT joined table, you usually end up with turning it into an INNER JOIN, because every row in the result has to meet that condition. JOIN conditions, on the other hand, only affect the JOIN, and only affect which rows from the JOINed table will succeed in getting joined. Hopefully, that can make it even more clear as to which thing belongs where in your queries.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2018 at 7:54 am
Eirikur Eiriksson - Tuesday, August 14, 2018 12:39 AMandycadley - Monday, August 13, 2018 2:05 PMNikosV - Monday, August 13, 2018 1:55 PMIn other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.A "working" join doesn't necessarily need to end up with a 1:1 relationship between rows of joined tables. Sometimes that's what you want, in which case you arrange the joining conditions to satisfy that requirement, but a lot of times you want to multiply up rows because that allows you to accomplish things that a purely 1:1 relationship would not.
He he, think of dbo.Me joining dbo.Girlfriends, any kind of aggregation when joining the two would end up in an explosive situation😀
😎
Hmmm... you need to aggregate in order to explode? All I ever recall needing was a concerted effort at INSERT. 😀😀😀😀
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2018 at 7:16 am
sgmunson - Tuesday, August 14, 2018 7:54 AMEirikur Eiriksson - Tuesday, August 14, 2018 12:39 AMandycadley - Monday, August 13, 2018 2:05 PMNikosV - Monday, August 13, 2018 1:55 PMIn other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.A "working" join doesn't necessarily need to end up with a 1:1 relationship between rows of joined tables. Sometimes that's what you want, in which case you arrange the joining conditions to satisfy that requirement, but a lot of times you want to multiply up rows because that allows you to accomplish things that a purely 1:1 relationship would not.
He he, think of dbo.Me joining dbo.Girlfriends, any kind of aggregation when joining the two would end up in an explosive situation😀
😎Hmmm... you need to aggregate in order to explode? All I ever recall needing was a concerted effort at INSERT. 😀😀😀😀
I'd think not using the aggregate function CURRENT would be more likely to result in an explosion. 😉
August 15, 2018 at 8:00 am
crmitchell - Wednesday, August 15, 2018 7:16 AMsgmunson - Tuesday, August 14, 2018 7:54 AMEirikur Eiriksson - Tuesday, August 14, 2018 12:39 AMandycadley - Monday, August 13, 2018 2:05 PMNikosV - Monday, August 13, 2018 1:55 PMIn other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.A "working" join doesn't necessarily need to end up with a 1:1 relationship between rows of joined tables. Sometimes that's what you want, in which case you arrange the joining conditions to satisfy that requirement, but a lot of times you want to multiply up rows because that allows you to accomplish things that a purely 1:1 relationship would not.
He he, think of dbo.Me joining dbo.Girlfriends, any kind of aggregation when joining the two would end up in an explosive situation😀
😎Hmmm... you need to aggregate in order to explode? All I ever recall needing was a concerted effort at INSERT. 😀😀😀😀
I'd think not using the aggregate function CURRENT would be more likely to result in an explosion. 😉
Huh? Never head of an aggregate function named CURRENT in any SQL variant that I know of... and you DO realize I was joking around with the reference to INSERT and explosion and the post from Eirikur that mentions JOIN-ing dbo.Me with dbo.Girlfriends, right?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2018 at 8:42 am
sgmunson - Wednesday, August 15, 2018 8:00 AMcrmitchell - Wednesday, August 15, 2018 7:16 AMsgmunson - Tuesday, August 14, 2018 7:54 AMEirikur Eiriksson - Tuesday, August 14, 2018 12:39 AMandycadley - Monday, August 13, 2018 2:05 PMNikosV - Monday, August 13, 2018 1:55 PMIn other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.A "working" join doesn't necessarily need to end up with a 1:1 relationship between rows of joined tables. Sometimes that's what you want, in which case you arrange the joining conditions to satisfy that requirement, but a lot of times you want to multiply up rows because that allows you to accomplish things that a purely 1:1 relationship would not.
He he, think of dbo.Me joining dbo.Girlfriends, any kind of aggregation when joining the two would end up in an explosive situation😀
😎Hmmm... you need to aggregate in order to explode? All I ever recall needing was a concerted effort at INSERT. 😀😀😀😀
I'd think not using the aggregate function CURRENT would be more likely to result in an explosion. 😉
Huh? Never head of an aggregate function named CURRENT in any SQL variant that I know of... and you DO realize I was joking around with the reference to INSERT and explosion and the post from Eirikur that mentions JOIN-ing dbo.Me with dbo.Girlfriends, right?
Now we are getting into the difficult area of temporal granularity 😀
😎
August 15, 2018 at 9:48 am
Eirikur Eiriksson - Wednesday, August 15, 2018 8:42 AMsgmunson - Wednesday, August 15, 2018 8:00 AMcrmitchell - Wednesday, August 15, 2018 7:16 AMsgmunson - Tuesday, August 14, 2018 7:54 AMEirikur Eiriksson - Tuesday, August 14, 2018 12:39 AMandycadley - Monday, August 13, 2018 2:05 PMNikosV - Monday, August 13, 2018 1:55 PMIn other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.A "working" join doesn't necessarily need to end up with a 1:1 relationship between rows of joined tables. Sometimes that's what you want, in which case you arrange the joining conditions to satisfy that requirement, but a lot of times you want to multiply up rows because that allows you to accomplish things that a purely 1:1 relationship would not.
He he, think of dbo.Me joining dbo.Girlfriends, any kind of aggregation when joining the two would end up in an explosive situation😀
😎Hmmm... you need to aggregate in order to explode? All I ever recall needing was a concerted effort at INSERT. 😀😀😀😀
I'd think not using the aggregate function CURRENT would be more likely to result in an explosion. 😉
Huh? Never head of an aggregate function named CURRENT in any SQL variant that I know of... and you DO realize I was joking around with the reference to INSERT and explosion and the post from Eirikur that mentions JOIN-ing dbo.Me with dbo.Girlfriends, right?
Now we are getting into the difficult area of temporal granularity 😀
😎
As in late night on the couch in the living room with both table objects present and ready to be joined ? 😀😀😀
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2018 at 10:33 am
sgmunson - Wednesday, August 15, 2018 9:48 AMEirikur Eiriksson - Wednesday, August 15, 2018 8:42 AMsgmunson - Wednesday, August 15, 2018 8:00 AMcrmitchell - Wednesday, August 15, 2018 7:16 AMsgmunson - Tuesday, August 14, 2018 7:54 AMEirikur Eiriksson - Tuesday, August 14, 2018 12:39 AMandycadley - Monday, August 13, 2018 2:05 PMNikosV - Monday, August 13, 2018 1:55 PMIn other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.A "working" join doesn't necessarily need to end up with a 1:1 relationship between rows of joined tables. Sometimes that's what you want, in which case you arrange the joining conditions to satisfy that requirement, but a lot of times you want to multiply up rows because that allows you to accomplish things that a purely 1:1 relationship would not.
He he, think of dbo.Me joining dbo.Girlfriends, any kind of aggregation when joining the two would end up in an explosive situation😀
😎Hmmm... you need to aggregate in order to explode? All I ever recall needing was a concerted effort at INSERT. 😀😀😀😀
I'd think not using the aggregate function CURRENT would be more likely to result in an explosion. 😉
Huh? Never head of an aggregate function named CURRENT in any SQL variant that I know of... and you DO realize I was joking around with the reference to INSERT and explosion and the post from Eirikur that mentions JOIN-ing dbo.Me with dbo.Girlfriends, right?
Now we are getting into the difficult area of temporal granularity 😀
😎As in late night on the couch in the living room with both table objects present and ready to be joined ? 😀😀😀
I don't think those are coffee tables😛
😎
August 15, 2018 at 1:41 pm
Eirikur Eiriksson - Wednesday, August 15, 2018 10:33 AMsgmunson - Wednesday, August 15, 2018 9:48 AMEirikur Eiriksson - Wednesday, August 15, 2018 8:42 AMsgmunson - Wednesday, August 15, 2018 8:00 AMcrmitchell - Wednesday, August 15, 2018 7:16 AMsgmunson - Tuesday, August 14, 2018 7:54 AMEirikur Eiriksson - Tuesday, August 14, 2018 12:39 AMandycadley - Monday, August 13, 2018 2:05 PMNikosV - Monday, August 13, 2018 1:55 PMIn other words, a working Join presumes that we are joining ONE value (PolicyNumber from Table A) to ONE value (PolicyNumber in Table 2). I'm not sure if I'm making any sense but I really need to get a hang of this.A "working" join doesn't necessarily need to end up with a 1:1 relationship between rows of joined tables. Sometimes that's what you want, in which case you arrange the joining conditions to satisfy that requirement, but a lot of times you want to multiply up rows because that allows you to accomplish things that a purely 1:1 relationship would not.
He he, think of dbo.Me joining dbo.Girlfriends, any kind of aggregation when joining the two would end up in an explosive situation😀
😎Hmmm... you need to aggregate in order to explode? All I ever recall needing was a concerted effort at INSERT. 😀😀😀😀
I'd think not using the aggregate function CURRENT would be more likely to result in an explosion. 😉
Huh? Never head of an aggregate function named CURRENT in any SQL variant that I know of... and you DO realize I was joking around with the reference to INSERT and explosion and the post from Eirikur that mentions JOIN-ing dbo.Me with dbo.Girlfriends, right?
Now we are getting into the difficult area of temporal granularity 😀
😎As in late night on the couch in the living room with both table objects present and ready to be joined ? 😀😀😀
I don't think those are coffee tables😛
😎
Knock 😀 on Wood ??? 😀
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 16, 2018 at 1:02 am
OK Guys here it goes.
I want to add a column to an existing result set but I'm having problems with the results coming back.
This is what I have so far for a given period.
DECLARE @DateFrom DATE = '2018-01-01'
DECLARE @DateTo DATE = '2018-08-08'
SELECT GP.ParameterValue AS ClaimType,
COUNT(IClass.ClientNumber) AS IndividualCases,
COUNT(CClass.ClientNumber) AS CompanyCases,
COUNT(IClass.ClientNumber) + COUNT(CClass.ClientNumber) AS TotalCases
--Claim Descriptions
FROM Company.alis.GeneralParameter GP
LEFT OUTER JOIN Company.alis.ClaimMain CM
ON GP.ParameterId = CM.ClaimType
--Get Individuals
LEFT OUTER JOIN (
SELECT ClientNumber,ClientClass,
ROW_NUMBER()OVER(PARTITION BY ClientNumber ORDER BY ClientNumber)#Row
FROM Company.alis.ClientDetails CD
WHERE ClientClass=1
GROUP BY ClientNumber,
ClientClass
)IClass
ON CM.ClientNumber = IClass.ClientNumber --Get Companies
LEFT OUTER JOIN (
SELECT ClientNumber,ClientClass,
ROW_NUMBER()OVER(PARTITION BY ClientNumber ORDER BY ClientNumber)#Row
FROM Company.alis.ClientDetails
WHERE ClientClass=2
GROUP BY ClientNumber,
ClientClass
)CClass
ON CM.ClientNumber = CClass.ClientNumber
WHERE GP.ParameterName='ClaimType'
AND CM.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo GROUP BY GP.ParameterValue
The above code returns what's in the attached file (Results.txt). Sorry for the attachment the formatting was really bad.
The results, come back correct.
What I need to do now, is add to the above code another column called SumInsured from a table called PolicyCover with these conditions.
1. For each ClaimType, Get total of SumAssured for every client that has made a claim with a client class of '1'. For this, three tables are needed.
1.
USE [Company]
GO
SELECT [PolicyNumber]
,[ClientNumber]
,[ClaimNumber]
,[ClaimType]
,[ClaimSubType]
,[ClaimPriceDate]
,[ClaimDescription]
,[OpeningRegistrationDate]
,[ClosingRegistrationDate]
,[OpeningEffectiveDate]
,[ClosingEffectiveDate]
,[OpeningReference]
,[ClosingReference]
,[ClosingStatus]
,[CurrentStatus]
,[PreviousCurrentStatus]
,[NewCurrentStatusFlag]
,[CalculationStatus]
,[DisconnectFromScheme]
,[ClaimEventDate]
,[BackdatedReasonCode]
,[ClaimNotificationDate]
,[PhasedRetireNumberOfSegments]
,[InlandRevenueRegimeCode]
,[PenaltyDiscountPercentage]
,[PenaltyDiscountReference]
,[MaxLumpSum]
,[ManualMaxLumpSumCode]
,[ManualMaxLumpSumRef]
,[MaxPension]
,[ManualMaxPensionCode]
,[ManualMaxPensionReference]
,[ManualSalaryDetailsCode]
,[FinalRemuneration]
,[ProtectedDecisionCode]
,[ProtectedDecisionDescription]
,[NonProtectedDecisionCode]
,[NprDecisionSpecifiedSum]
,[NprDecisionDescription]
,[AnnuitySpousePerc]
,[AnnuityGuaranteedPeriod]
,[AnnuityEscalationMethod]
,[AnnuityEscalationRate]
,[AnnuityArrearsAdvanceCode]
,[AnnuityPaymentFreq]
,[AnnuityOverlapOption]
,[AnnuityGuarRemainPayCode]
,[FinalRemunerationManualFlag]
,[InlandRevenuRegiManualFlag]
,[StandardAnnuityCommission]
,[AnnuityCommissionBiloc]
,[AnnuityCommissionOrBonus]
,[PrimaryMedicalReason]
,[SecondaryMedicalReason]
,[CancellationReasonCode]
,[ExternalReference]
,[TaxInSourceFlag]
,[PhasedClaimFlag]
,[PhasedClaimFreq]
,[PhasedClaimEndDateMethod]
,[PhasedClaimEndDateValue]
,[PhasedClaimMinAmount]
,[NextClaimEffecDate]
,[MinimumResidualAmount]
,[AssignedTeamNo]
,[AssignedUserId]
,[TerminationEffecDate]
,[PendingReasonCode]
,[DeclineReasonCode]
,[ClaimReviewDate]
,[ClaimElapseDate]
,[RequestedAmount]
,[RequestType]
,[ApprovalReasonCode]
,[RescissionReasonCode]
,[MainClaimNumber]
,[ManualReviewDateFlag]
,[TerminationReasonCode]
,[ReviewDueDealtFlag]
,[InitialNoteReasonCode]
,[OverruledReasonCode]
,[BandNumber]
,[OriginalClaimNumber]
,[TaxDistributionCode]
,[CategoryNumber]
,[DiagnosisCode1]
,[DiagnosisCode2]
,[DiagnosisCode3]
,[DiagnosisCode4]
,[DiagnosisCode5]
,[ClientEventNumber]
,[QuoteReasonCode]
,[RequestorNumber]
,[OriginalOpeningRegistrationDate]
,[UserId]
,[PClaimMainPK]
,[LoadingTimestamp]
FROM [alis].[ClaimMain]
GO
2.
USE [Company]
GO
SELECT [ClientNumber]
,[Title]
,[FirstName]
,[LastName]
,[MiddleInitial]
,[IdentificationType]
,[IdentificationNumber]
,[ExternalReference]
,[ClientClass]
,[ClientType]
,[PreferredLanguage]
,[OpeningRegistrationDate]
,[ClosingRegistrationDate]
,[OpeningEffectiveDate]
,[ClosingEffectiveDate]
,[OpeningStatus]
,[ClosingStatus]
,[OpeningReference]
,[ClosingReference]
,[UserId]
,[MClientsPk]
,[LoadingTimestamp]
FROM [alis].[ClientDetails]
GO
3.
USE [Company]
GO
SELECT [PolicyNumber]
,[CoverNumber]
,[CoverClientRole]
,[CoverCommencementDate]
,[BasicCoverFlag]
,[InternalCoverNumber]
,[LayerNumber]
,[MainLayerFlag]
,[Currency]
,[SumAssured]
,[AnnualPremium]
,[TerminationDate]
,[DurationInYears]
,[ContributionType]
,[InstructionRecordCode]
,[LastRenewalDate]
,[DeferralPeriod]
,[BenefitPaymentPeriod]
,[BenefitPaymentType]
,[BenefitPaymentPeriodType]
,[ActuaryTable]
,[ActuaryTableEdition]
,[OpeningRegistrationDate]
,[ClosingRegistrationDate]
,[OpeningEffectiveDate]
,[ClosingEffectiveDate]
,[OpeningStatus]
,[ClosingStatus]
,[OpeningReference]
,[ClosingReference]
,[UserId]
,[PCoversPk]
,[LoadingTimestamp]
FROM [alis].[PolicyCover]
GO
Table 1 contains the claims, Table 2 contains the ClientClass, Table 3 contains the Sum. You'll notice that I've included all those tables in the code I've mentioned. I'm having trouble with the Joins.
The expected result is attached. Again, the formatting is bad so I uploaded a file.
So basically, for every claim type I'm showing certain details. Problem is, in PolicyCover, the same PolicyNumber may exist multiple times, each one with a different SumAssured, it may be 0.0000 or it may be not be. The reason why seperate rows return for the same PolicyNumber is because each row is actually distinct, it has other irrelevant to us details that make each record differ like OpeningStatus, ClosingStatus etc.
I'm not sure what else I'll need to clarify. If anything, let me know.
For all it's worth, I've added code to get SumInsured for each PolicyNumber, my problem now is making sure those PolicyNumbers are of ClientClass 1. The ClientClass is in the ClientDetails table and that table can Join onto ClaimMain, and ClaimMain can join onto PolicyNumber where the SumAssured is. If I join those tables, I get wierd results back. That's exactly my problem here.
Updated Code
DECLARE @DateFrom DATE = '2018-01-01'
DECLARE @DateTo DATE = '2018-08-08'
SELECT GP.ParameterValue AS ClaimType,
COUNT(IClass.ClientNumber) AS IndividualCases,
COUNT(CClass.ClientNumber) AS CompanyCases,
COUNT(IClass.ClientNumber) + COUNT(CClass.ClientNumber) AS TotalCases
--Claim Descriptions
FROM Company.alis.GeneralParameter GP
LEFT OUTER JOIN Company.alis.ClaimMain CM
ON GP.ParameterId = CM.ClaimType
--Get Individuals
LEFT OUTER JOIN (
SELECT ClientNumber,ClientClass,
ROW_NUMBER()OVER(PARTITION BY ClientNumber ORDER BY ClientNumber)#Row
FROM Company.alis.ClientDetails CD
WHERE ClientClass=1
GROUP BY ClientNumber,
ClientClass
)IClass
ON CM.ClientNumber = IClass.ClientNumber --Get Companies
LEFT OUTER JOIN (
SELECT ClientNumber,ClientClass,
ROW_NUMBER()OVER(PARTITION BY ClientNumber ORDER BY ClientNumber)#Row
FROM Company.alis.ClientDetails
WHERE ClientClass=2
GROUP BY ClientNumber,
ClientClass
)CClass
ON CM.ClientNumber = CClass.ClientNumber
--Get SumAssured for Individuals. Need to make sure these PolicyNumbers are of Client class 1 type.
LEFT OUTER JOIN (
SELECT PolicyNumber, SUM(SumAssured) AS SumInsured
FROM Company.alis.PolicyCover
WHERE SumAssured <> 0 AND ClosingStatus=0
GROUP BY PolicyNumber
)SumInsured
ON CM.PolicyNumber = SumInsured.PolicyNumber
WHERE GP.ParameterName='ClaimType'
AND CM.OpeningRegistrationDate BETWEEN @DateFrom AND @DateTo GROUP BY GP.ParameterValue
August 16, 2018 at 6:12 am
You've placed the SumInsured query inside of a LEFT JOIN, but not done any JOINs within that SumInsured query to ensure it's the Client class 1 type. You can add joins within your SumInsured query for that purpose... But if you get incorrect results because there may be more than 1 row in one of the tables, then you need criteria that can limit the rows returned from the JOINed tables that you add, which would be best as JOIN conditions.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 16, 2018 at 6:29 am
sgmunson - Thursday, August 16, 2018 6:12 AMYou've placed the SumInsured query inside of a LEFT JOIN, but not done any JOINs within that SumInsured query to ensure it's the Client class 1 type. You can add joins within your SumInsured query for that purpose... But if you get incorrect results because there may be more than 1 row in one of the tables, then you need criteria that can limit the rows returned from the JOINed tables that you add, which would be best as JOIN conditions.
OK. I got the gist now. Tried it and so far it's looking good. Hopefully the user will agree on Monday. Fingers crossed.
Please, tell me that Joins are a general pain in the arse and that it's not just my arse.
One last question, what do you mean by join condition?
If the join condition is after the ON keyword then I guess I need common ground. ClientDetails which contains ClientClass can't join onto PolicyCover. How would I add an extra join condition?
Otherwise, I don't know what a Join condition is...
August 16, 2018 at 6:45 am
NikosV - Thursday, August 16, 2018 6:29 AMsgmunson - Thursday, August 16, 2018 6:12 AMYou've placed the SumInsured query inside of a LEFT JOIN, but not done any JOINs within that SumInsured query to ensure it's the Client class 1 type. You can add joins within your SumInsured query for that purpose... But if you get incorrect results because there may be more than 1 row in one of the tables, then you need criteria that can limit the rows returned from the JOINed tables that you add, which would be best as JOIN conditions.OK. I got the gist now. Tried it and so far it's looking good. Hopefully the user will agree on Monday. Fingers crossed.
Please, tell me that Joins are a general pain in the arse and that it's not just my arse.
One last question, what do you mean by join condition?
If the join condition is after the ON keyword then I guess I need common ground. ClientDetails which contains ClientClass can't join onto PolicyCover. How would I add an extra join condition?
Otherwise, I don't know what a Join condition is...
A JOIN condition is the pieces of the join that look like WHERE clause material. In your query, you have this snippet: --Get SumAssured for Individuals. Need to make sure these PolicyNumbers are of Client class 1 type.
LEFT OUTER JOIN (
SELECT PolicyNumber, SUM(SumAssured) AS SumInsured
FROM Company.alis.PolicyCover
WHERE SumAssured <> 0 AND ClosingStatus=0
GROUP BY PolicyNumber
)SumInsured
ON CM.PolicyNumber = SumInsured.PolicyNumber
I've bolded, italicized, and underlined the JOIN condition. In order for the JOIN to succeed for a given row, the values of PolicyNumber must match. You can use any additional condition that you need, and you can use both AND and OR to separate the individual conditions, based on whatever logic you need to restrict the rows returned by the JOIN.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 16, 2018 at 6:47 am
NikosV - Thursday, August 16, 2018 6:29 AMsgmunson - Thursday, August 16, 2018 6:12 AMYou've placed the SumInsured query inside of a LEFT JOIN, but not done any JOINs within that SumInsured query to ensure it's the Client class 1 type. You can add joins within your SumInsured query for that purpose... But if you get incorrect results because there may be more than 1 row in one of the tables, then you need criteria that can limit the rows returned from the JOINed tables that you add, which would be best as JOIN conditions.OK. I got the gist now. Tried it and so far it's looking good. Hopefully the user will agree on Monday. Fingers crossed.
Please, tell me that Joins are a general pain in the arse and that it's not just my arse.
One last question, what do you mean by join condition?
If the join condition is after the ON keyword then I guess I need common ground. ClientDetails which contains ClientClass can't join onto PolicyCover. How would I add an extra join condition?
Otherwise, I don't know what a Join condition is...
JOINs are NOT the pain in the arse.... It's dealing with people who don't understand the nature of their data that is the largest source of pain. You're just learning the JOIN stuff, and it's not easy when you're just getting started. Maybe it will help to just recognize that learning SQL as opposed to a procedural language like VB or C#, is a paradigm shift, requiring you to think not about what you can do to a row, but instead, think about what you can do to a column...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 16, 2018 at 7:46 am
sgmunson - Thursday, August 16, 2018 6:47 AMNikosV - Thursday, August 16, 2018 6:29 AMsgmunson - Thursday, August 16, 2018 6:12 AMYou've placed the SumInsured query inside of a LEFT JOIN, but not done any JOINs within that SumInsured query to ensure it's the Client class 1 type. You can add joins within your SumInsured query for that purpose... But if you get incorrect results because there may be more than 1 row in one of the tables, then you need criteria that can limit the rows returned from the JOINed tables that you add, which would be best as JOIN conditions.OK. I got the gist now. Tried it and so far it's looking good. Hopefully the user will agree on Monday. Fingers crossed.
Please, tell me that Joins are a general pain in the arse and that it's not just my arse.
One last question, what do you mean by join condition?
If the join condition is after the ON keyword then I guess I need common ground. ClientDetails which contains ClientClass can't join onto PolicyCover. How would I add an extra join condition?
Otherwise, I don't know what a Join condition is...JOINs are NOT the pain in the arse.... It's dealing with people who don't understand the nature of their data that is the largest source of pain. You're just learning the JOIN stuff, and it's not easy when you're just getting started. Maybe it will help to just recognize that learning SQL as opposed to a procedural language like VB or C#, is a paradigm shift, requiring you to think not about what you can do to a row, but instead, think about what you can do to a column...
Thats good to know. What about the question on the join condition I asked?
The last bit you said was right. I was always using c# and vb so that shift is quite tricky.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply