Joining Problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Joining Problems

Author

Topic

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-26 : 08:16:10

Hello,

Can anyone help me with where I'm going wrong with this query.

I'm quering an insurance database to return the number of claims and notifications received last month and the number of claims paid last month. Here's what I have:


SELECT  Count(case when Convert(datetime, c.ExtraInfo2, 103) between dateadd(month, datediff(month, 0, getdate()) - 1, 0)
		and	dateadd(month, datediff(month, 0, getdate()), -1) then Convert(datetime, c.ExtraInfo2, 103) end) as 'Notifications Received',
		Count(case when c.ClaimDate between	dateadd(month, datediff(month, 0, getdate()) - 1, 0)
		and	dateadd(month, datediff(month, 0, getdate()), -1) and c.ClaimStatus <>'Notification' then c.ClaimDate end) as 'Claims Received', 
		Count(case when cs.SettlementDate between dateadd(month, datediff(month, 0, getdate()) - 1, 0)
		and	dateadd(month, datediff(month, 0, getdate()), -1)  and cs.SettlementType = 'C' then cs.SettlementDate end) as 'Claims Paid'
FROM dbo.tblClaim As c 
LEFT JOIN dbo.tblClaimSettlement As cs
ON c.Rowguid = cs.Rowguid

The problem is where the tables are joined.

If I use Left Join I get:

Notifications:19 Claims:12 Paid:0

If I use Right Join I get:

Notifications:0 Claims:0 Paid:22

The numbers returned are correct but should be:

Notifications:19 Claims:12 Paid:22

Any ideas?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-26 : 08:21:35

This is probably because you are joining on the wrong columns. You can test that theory easily by using an INNER JOIN. And, you would get zero rows.

Rowguid sounds like something that is an automatically generated ID or something along those lines. If you have a claimId or something similar that is common in both tables, that is what you need to join on.

...
FROM
	dbo.tblClaim AS c
	LEFT JOIN dbo.tblClaimSettlement AS cs
		ON  c.ClaimId= cs.ClaimId

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-26 : 08:38:47

Rowguid probably is auto generated as there is no feild in the db for it but its the only column, when joined, that gives me the right answers.

There is a ClaimID but when I use this the figures in the result are not correct.

Rowguid is the primary key in dbo.tblClaimSettlements and is also in dbo.tblClaim

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-26 : 08:55:08

OK to clarify;

when I use:

FROM
dbo.tblClaim AS c
LEFT JOIN dbo.tblClaimSettlement AS cs
ON c.ClaimId= cs.ClaimId

I do get a result in each column but I get:

Notifications:20 Claims:13 Paid:22

There is one claim and notification that duplictes in each column pushing the figures out by one. If i can resolve this issue then problem solved... Any ideas?

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-26 : 09:10:05

It may be that you have more than one row for a given claim id in one of the two tables. You can test that using the following queries. If you do find more than one row for a given claim id, is that a valid condition? Perhaps one of the rows was cancelled and superseded by the other row? Or may be the two rows need to be aggregated?

SELECT ClaimId FROM dbo.tblClaim GROUP BY ClaimId HAVING COUNT(*) > 1;

SELECT ClaimId FROM dbo.tblClaimSettlement GROUP BY ClaimId HAVING COUNT(*) > 1;

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-26 : 09:39:53

The issue is with dbo.tblClaimSettlement where a second row is created when two payments are made. This is valid but i need to only count it once as it is still only one claim.

So, I need to count distinct ClaimID which I have tried before but cant get to work. Do you know where I should put the DISTINCT in my query to make it work for each column?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-26 : 10:00:55

I don't really know, because I don't know your business rules. One possibility is this:


...
FROM
	dbo.tblClaim AS c
	LEFT JOIN 
	(
		SELECT DISTINCT ClaimId, SettlementDate, SettlementType
		FROM dbo.tblClaimSettlement
	) AS cs
		ON  c.ClaimId = cs.ClaimId

But, that may not get you what you want if the SettlementDate and/or SettlementType are different for the duplicate rows. Another possibility is to pick the row with the latest SettlementDate, like this:

FROM
	dbo.tblClaim AS c
	LEFT JOIN 
	(
		SELECT TOP 1 ClaimId, SettlementDate, SettlementType
		FROM dbo.tblClaimSettlement
		ORDER BY SettlementDate DESC
	) AS cs
		ON  c.ClaimId = cs.ClaimId

If neither of this gets you what you want, can you describe the rule that you want to use for picking one out of these two duplicate rows?

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-26 : 11:05:25

Both of your suggestions work but give me a zero in the third column as I was getting before.

Is there anyway I can get the first two columns to generate their data before joing to dbo.tblClaimSettlement? As, its only when the tables are joined that the error occurs. The data for the first two columns is in dbo.tblClaim and do not need to reference dbo.tblClaimSettlement.

I only need to join dbo.tblClaimSettlement for the third column.

Thanks for your help with this, I appreciate that I'm being a bit vague but I've been dropped in at the deep end on this.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-26 : 11:11:40

I am afraid I did not quite follow what you are saying. Can you post some sample data that demonstrates the issue? If you need help posting, take a look at Brett's blog here: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

If you want to pick the first two columns, would this work?

FROM
	dbo.tblClaim AS c
	LEFT JOIN 
	(
		SELECT ClaimId, SettlementDate, MAX(SettlementType) AS SettlementType
		FROM dbo.tblClaimSettlement
		GROUP BY ClaimId, SettlementDate
	) AS cs
		ON  c.ClaimId = cs.ClaimId

sql_monkey
Starting Member

19 Posts

Posted - 2012-03-26 : 11:41:04

OK no problem I've discovered what the issue was. A claim settlement had been created but no data added then a second settlement was created in the same claim causing it to be counted twice. I removed the empty feild and I now get the answer I'm looking.

Not sure why or how but I'll keep an eye out for it happenning again in future.

Thank you sunitabeck, your help has been invaluable.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-26 : 12:08:49

Glad you worked it out :)

As for maintaining data consistency, that is one of the things that SQL Server (and relational databases in general) are good at. How you enforce that depends on the nature of your requirements.

For example, if you should have unique claim id's in the Claims table, you can create a uniqueness constraint on the claim id column. If someone should not be allowed to create a ClaimSettlement row without a valid claim id that already exists in the Claims table, you can create a foreign key constraint.

You may also do consistency checks when processing the data if the constraint/data integrity condition is too hard or impossible to enforce as a SQL constraint.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources