Many to many relationships - 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 2000 Forums

Transact-SQL (2000)

Many to many relationships

Author

Topic

rgjb
Starting Member

19 Posts

Posted - 2004-07-27 : 19:04:43

Hi everyone.
Hoping that someone is able to assist with this.
Background: We have two tables the APPOINTEE_HISTORY and the APPOINTEE_RREMUNERATION_HISTORY. What I need to be able to do is to marry the data up so that for each row for an employee in the APPOINTEE_HISTORY to show the corresponding row from the APPOINTEE_REMUNERATION_HISTORY table (if it exists).
Generally for each row from the APPOINTEE_HISTORY table, if an employee has an allowance then there is a corresponding record in the APPOINTEE_REMUNERATION_HISTORY. However that is not always the case, so an allowance might flow across change_dates until the allowance end_date.

Anyway here are the create tables etc and at the bottom the desired output which I hope makes things a little clearer. And in case you're wondering - I can't change the data structure as it is a third party product.

Many thanks

CREATE TABLE [dbo].[APPOINTEE_HISTORY] (
[EMP_NO] [char] (10),
[CHANGE_DATE] [datetime],
[BASIC_PAY] [float],
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[APPOINTEE_REMUNERATION_HISTORY] (
[EMP_NO] [char] (10),
[ALLOWANCE] [char] (10),
[EFFECTIVE_DATE] [datetime],
[END_DATE] [datetime],
[AMOUNT] [float] NULL ,
) ON [PRIMARY]
GO

APPOINTEE_HISTORY
Emp_no change_date Basic_Pay
555555 2002-06-01 58012.0
555555 2003-04-01 60000.0
555555 2004-04-01 62800.0
555555 1999-05-01 45600.0
555555 2000-04-01 48000.0
555555 2000-09-01 45600.0
555555 2001-02-23 45600.0
555555 2001-03-01 55440.0
555555 1998-03-16 38000.0
555555 1998-11-01 36100.0

APPOINTEE_REMUNERATION_HISTORY
Emp_No Allowance Effective_Date End_Date Amount
555555 BONUS 1998-03-16 2000-08-31 3000.0
555555 BONUS 2000-09-01 2002-05-31 6000.0
555555 ACCUM% 1998-11-01 2002-05-31 1900.0

Output

Emp_No change_Date Basic_Pay Allowance Amount
555555 1998-03-16 38000.0 BONUS 3000
555555 1998-11-01 36100.0 ACCUM 1900
555555 1999-05-01 45600.0 BONUS 3000
555555 1999-05-01 45600.0 ACCUM 1900
555555 2000-04-01 48000.0 BONUS 3000
555555 2000-04-01 48000.0 ACCUM 1900
555555 2000-09-01 45600.0 BONUS 6000
555555 2000-09-01 45600.0 ACCUM 1900
555555 2001-02-23 45600.0 BONUS 6000
555555 2001-02-23 45600.0 ACCUM 1900
555555 2001-03-01 55440.0 BONUS 6000
555555 2001-03-01 55440.0 ACCUM 1900
555555 2002-06-01 58012.0
555555 2003-04-01 60000.0
555555 2004-04-01 62800.0

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-27 : 19:13:54

You need to use a LEFT JOIN.

Fire up books on-line and search for LEFT JOIN in the index section.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

rgjb
Starting Member

19 Posts

Posted - 2004-07-27 : 19:29:43

Hi Michael,
Thanks for that but unfortunately it doesn't give me the require response. If I do the aleft join then i seem to get more data than I actually want. If I execute the following statement:

select ah.emp_no, ah.change_date, ah.basic_pay,
arh.allowance, arh.amount from appointee_history ah
left join appointee_remuneration_history arh on
ah.emp_no = arh.emp_no where ah.emp_no = 555555

I get this:

555555 2002-06-01 00:00:00.000 58012.0 BONUS 3000.0
555555 2002-06-01 00:00:00.000 58012.0 BONUS 6000.0
555555 2002-06-01 00:00:00.000 58012.0 ACCUM 1900.0
555555 2003-04-01 00:00:00.000 60000.0 BONUS 3000.0
555555 2003-04-01 00:00:00.000 60000.0 BONUS 6000.0
555555 2003-04-01 00:00:00.000 60000.0 ACCUM 1900.0
555555 2004-04-01 00:00:00.000 62800.0 BONUS 3000.0
555555 2004-04-01 00:00:00.000 62800.0 BONUS 6000.0
555555 2004-04-01 00:00:00.000 62800.0 ACCUM 1900.0
555555 1999-05-01 00:00:00.000 45600.0 BONUS 3000.0
555555 1999-05-01 00:00:00.000 45600.0 BONUS 6000.0
555555 1999-05-01 00:00:00.000 45600.0 ACCUM 1900.0
555555 2000-04-01 00:00:00.000 48000.0 BONUS 3000.0
555555 2000-04-01 00:00:00.000 48000.0 BONUS 6000.0
555555 2000-04-01 00:00:00.000 48000.0 ACCUM 1900.0
555555 2000-09-01 00:00:00.000 45600.0 BONUS 3000.0
555555 2000-09-01 00:00:00.000 45600.0 BONUS 6000.0
555555 2000-09-01 00:00:00.000 45600.0 ACCUM 1900.0
555555 2001-02-23 00:00:00.000 45600.0 BONUS 3000.0
555555 2001-02-23 00:00:00.000 45600.0 BONUS 6000.0
555555 2001-02-23 00:00:00.000 45600.0 ACCUM 1900.0
555555 2001-03-01 00:00:00.000 55440.0 BONUS 3000.0
555555 2001-03-01 00:00:00.000 55440.0 BONUS 6000.0
555555 2001-03-01 00:00:00.000 55440.0 ACCUM 1900.0
555555 1998-03-16 00:00:00.000 38000.0 BONUS 3000.0
555555 1998-03-16 00:00:00.000 38000.0 BONUS 6000.0
555555 1998-03-16 00:00:00.000 38000.0 ACCUM 1900.0
555555 1998-11-01 00:00:00.000 36100.0 BONUS 3000.0
555555 1998-11-01 00:00:00.000 36100.0 BONUS 6000.0
555555 1998-11-01 00:00:00.000 36100.0 ACCUM 1900.0

cheers
Gregg

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-27 : 19:36:28

Hmm, let me look at this again...

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-27 : 19:50:34

Try this, it looks right. I'm concerned about the 1998 Bonus row. That's not in your sample output. Is that an oversight or is my cod wrong?


CREATE TABLE #APPOINTEE_HISTORY(Emp_No INT, Change_Date DATETIME, BasicPay Money)
CREATE TABLE #APPOINTEE_REMUNERATION_HISTORY(Emp_No INT, Allowance VARCHAR(20), Effective_Date DATETIME, End_Date DATETIME, Amount MONEY)

INSERT INTO #APPOINTEE_HISTORY(Emp_No, Change_Date, BasicPay)
VALUES(555555, '1998-03-16', 38000.0)

INSERT INTO #APPOINTEE_HISTORY(Emp_No, Change_Date, BasicPay)
VALUES(555555, '1998-11-01', 36100.0)

INSERT INTO #APPOINTEE_HISTORY(Emp_No, Change_Date, BasicPay)
VALUES(555555, '1999-05-01', 45600.0)


INSERT INTO #APPOINTEE_REMUNERATION_HISTORY(Emp_No, Allowance, Effective_Date, End_Date, Amount)
VALUES(555555, 'BONUS', '1998-03-16',  '2000-08-31', 3000.0) 


INSERT INTO #APPOINTEE_REMUNERATION_HISTORY(Emp_No, Allowance, Effective_Date, End_Date, Amount)
VALUES(555555, 'ACCUM', '1998-11-01',  '2002-05-31', 1900.0) 



SELECT a.Emp_No, a.Change_Date, a.BasicPay, h.Allowance, h.Amount
FROM #APPOINTEE_HISTORY a
LEFT JOIN #APPOINTEE_REMUNERATION_HISTORY h ON h.Emp_No = a.Emp_No AND a.Change_Date BETWEEN h.Effective_Date AND h.End_Date
ORDER BY a.Change_Date

DROP TABLE #APPOINTEE_REMUNERATION_HISTORY
DROP TABLE #APPOINTEE_HISTORY

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

rgjb
Starting Member

19 Posts

Posted - 2004-07-27 : 22:33:05

Hi Michael,
I'll try this on some production data & see how it goes. I'll let you know
Many Thanks
Gregg

rgjb
Starting Member

19 Posts

Posted - 2004-07-28 : 00:54:03

Michael,

Mate, it works brilliantly. Thanks for your help.

Cheers
Gregg

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-07-28 : 12:59:33

NP man, glad I could help.I learned a few things solving that problem, so it's a win-win!

In the future, when you post a question like this it would be really handy if you could post CREATE TABLE and INSERT INTO statements that we can paste right into QA and start working on the problem.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources