Linking a complex one-to-many relationship

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)

Linking a complex one-to-many relationship

Author

Topic

jmiskey
Starting Member

15 Posts

Posted - 2008-02-29 : 17:18:47

I have two tables that I am trying to link together to get billing rates for specific time periods.

Table A has fields like (among many others):
-Client ID
-Plan Start Date
-Plan End Date
-Contact

Table B has fields like (among others):
-Client ID
-Fee Start Date
-Fee End Date
-Billing Rate

And a sample of data from each may look like:

Table A:
ABC Corp, 1/1/2007, 12/31/2007, Brian
ABC Corp, 1/1/2008, 12/31/2008, Jim

Table B:
ABC Corp, 1/1/2007, 12/31/2007, $100
ABC Corp, 1/1/2008, 04/30/2008, $100
ABC Corp, 5/1/2008, 12/31/2008, $75

And I want my results to look like:
ABC Corp, 1/1/2007, 12/31/2007, Brian, $100
ABC Corp, 1/1/2008, 04/30/2008, Jim, $100
ABC Corp, 5/1/2008, 12/31/2008, Jim, $75

The problem I am having is when table B has multiple records for the one year duration, how do I link the tables? The "split" during the year could happen at any point during the year.

Does this make sense? Can anyone help?

Thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-29 : 20:47:46

This looks like it would pull what you want per your sample..


Select a.ClientID,
       b.FeeStartDate,
       b.FeeEndDate,
       a.Contact,
       b.BillingRate
FROM TableA a inner join TableB b on a.ClientID = b.ClientID

Maybe you could shed light on the results you are getting that are wrong?

Poor planning on your part does not constitute an emergency on my part.

jmiskey
Starting Member

15 Posts

Posted - 2008-03-03 : 08:35:50

The query as you have written it would include 6 result rows (2 * 3) because it only includes Client ID in the join. I need to somehow include the Date fields in the join or the criteria so that I only get the three rows back I listed. Otherwise, it will be linking 2007 data to 2008 data, which is incorrect.

Note that in table A, the Plan Start Date and Plan End Dates span a full year (I should mention though, that the 12 month span is not always 1/1 - 12/31; we have some 4/1 - 3/31). This should always be the case. However, in Table B, the year may be broken up into multiple increments (like in my example). So basically, it probably makes the most sense to start from Table B, and try to find the corresponding Client ID/Date combination that corresponds to that record.

Does that make sense?

jmiskey
Starting Member

15 Posts

Posted - 2008-03-03 : 09:26:39

OK, I think I can do it just by adding a WHERE clause like this:

SELECT
a.ClientID, b.FeeStartDate, b.FeeEndDate, a.Contact, b.BillingRate
FROM TableA AS a INNER JOIN TableB AS b ON a.ClientID=b.ClientID
WHERE b.FeeStartDate>=a.PlanStartDate and b.FeeEndDate<=a.PlanEndDate;

jmiskey
Starting Member

15 Posts

Posted - 2008-03-03 : 09:26:39

OK, I think I can do it just by adding a WHERE clause like this:

SELECT
a.ClientID
, b.FeeStartDate
, b.FeeEndDate
, a.Contact
, b.BillingRate
FROM
TableA AS a
INNER JOIN
TableB AS b
ON
a.ClientID=b.ClientID
WHERE
b.FeeStartDate>=a.PlanStartDate
and b.FeeEndDate<=a.PlanEndDate;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-03 : 09:34:02

or simply:-

SELECT 
a.ClientID
, b.FeeStartDate
, b.FeeEndDate
, a.Contact
, b.BillingRate
FROM 
TableA AS a 
INNER JOIN 
TableB AS b 
ON 
a.ClientID=b.ClientID
AND b.FeeStartDate>=a.PlanStartDate 
AND b.FeeEndDate<=a.PlanEndDate;

jmiskey
Starting Member

15 Posts

Posted - 2008-03-05 : 16:51:00

I didn't realize that you could use > or < in JOIN expressions, I thought they could only be used in Criteria.

Is one more efficient than the other?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources