SQL Joins

Question

Post reply

SQL Joins

Ashok Jebaraj

Ten Centuries

Points: 1215
More actions
January 2, 2008 at 8:22 am

#377760

Hi , I have 2 tables [PremiumAmount] and [ClaimAmount]. The columns are very similar for both the tables.

[PremiumAmount]

CustomerID

PlanID

PremiumAmount

PremiumPaidDate

[ClaimAmount]

CustomerID

PlanID

ClaimAmount

ClaimDate

1 Customer can have any number of plans (max 3).

[PremiumAmount] stores the premium recieved from a customer for a plan and for the resopective month. Not all Customers pay premium correctly, however they are pay it in the next month with some fine. The [ClaimAmount] stpres the Claim's recieved from Physicians for each month for the respective customer and plan. The Physicians do not Claim money for all the customers and plans every month.

I need to write an SQL to get the total PremiumPaind, ClaimedAmount, for a respective customer and Plan and for the respective month.

This is the query I write but it does not seem to work correct.

select a.CustomerID, a.PlanID, b.ClaimAmount, a.PremiumAmount from PremiumAmount a

left outer join ClaimAmount b ON (a.CustomerID = b.CustomerID and a.PlanID = b.PlanID AND b.ClaimDate = a.PremiumPaidDate)

order by CustomerID, PlanID

Can someone help me out.

Let me know if you need a sample data.

Thank you,

Ashok Jebaraj

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adam Haines SSC-Insane Points: 23217 More actions · Answer 1

Adam Haines

SSC-Insane

Points: 23217

January 2, 2008 at 8:34 am

#764065

Can you provide any sample data? It would make things easier for the both of us and would provide a more timely solution to your problem.

Thanks.

Adam

My blog: http://jahaines.blogspot.com

Nic Hopper-586355 Ten Centuries Points: 1131 More actions · Answer 2

Hi there,

it looks like the data may not be coming back because you are using a date in your join

left outer join ClaimAmount b ON (a.CustomerID = b.CustomerID and a.PlanID = b.PlanID AND b.ClaimDate = a.PremiumPaidDate)

your basically saying

left outer join ClaimAmount b ON (123= 123 and 456= 456 AND '01/01/2008 00:00:00'= '01/01/2008 01:00:00')

I'm assuming that claimDate and PremiumPaidDate are true date times, if so unless the values match (including times) then you wont be able to make the join, you could remove them from the join, use greater or less (i.e claimdate > premiumpaiddate, or the you could cast/convert them to remove any times, this would return them (if the date are equal).

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

if you're willing to use temp tables, a union would make this very easy. assuming ClaimDate and PremiumPaidDate are smalldatetimes and ClaimAmount and PremiumAmount are both the same numeric datatypes, this should get you started:



select CustomerID, PlanID, convert( char(7), ClaimDate, 20 ) as YearMonth,

	ClaimAmount, ClaimAmount * 0 as PremiumAmount

into #cp

from ClaimAmount

-- add any criteria here

union

select CustomerID, PlanID, convert( char(7), PermiumPaidDate, 20 ) as YearMonth,

	PremiumAmount * 0 as ClaimAmount, PremiumAmount from PremiumAmount

-- repeat the criteria here

select CustomerID, PlanID, YearMonth,

	sum(ClaimAmount) as ClaimAmount, sum(PremiumAmount) as PremiumTotal

from #cp

group by CustomerID, PlanID, YearMonth