SQL Joins

Question

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

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: 1161 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

Note that YearMonth is output in YYYY-MM format.

Ashok Jebaraj Ten Centuries Points: 1215 More actions · Answer 4

Hello Thank you for your replies,

here is the sample data. The spread sheet contains the expected results also.

Thank you,

Ashok Jebaraj

Ashok Jebaraj Ten Centuries Points: 1215 More actions · Answer 5

Hello,

this works. I can manage to use temp tables.

Thank you,

Ashok Jebaraj