SQL group by question

I have two tables:
TABLE A
======
ID(primary key)
EMPLOYEECODE
AMOUNT
TRANSACTIONDATE

TABE B
======
ID(primary key)
EMPLOYEECODE
AMOUNT
TRANSACTIONDATE

The idea here is that table A keeps record of amount that a certain employee got PAID for any given day.
But there could be multiple rows for given employee on given day.
So if employee X got paid 100 dollars on 12/12/2001, then it may be:
1 row of employee X with 12/12/2001 and amount 100
OR
X number of rows of employee X with 12/12/2002 and sum(amount) = 100

now table B is a record of amount that was paid to employee.
It's same logic as table A.
If employee X got paid 100 dollars on 12/12/2001 then it maybe:
1 row of employee X with 12/12/2001 and amount 100
OR
X number of rows of employee X with 12/12/2001 and sum(Amount) = 100

Now I need to create stored procedure to match the two tables and record the ID from each table.
So let's say table A has
1
X
50
12/12/2001

2
X
50
12/12/2001

table B
--------
1
X
25
12/12/2001

2
X
75
12/12/2001

Now as you can see from table A, employee x got paid 100 dollars (50 + 50) and from table B employee x was given 100 dollars(25+75)
So the records are correct.
So I will record on some log table, ID's 1 and 2 from table A and ID's 1 and 2 from table B.

Now question is I have a lot of rows like this, that I need to check if amount from two sides are same or not based on employee code and transaction date.

So what is the best way to do this?
I would assume I could select table A grouped by employeecode and date and sum(amount) and try to match with group by of table B by employeecode and date and sum(amount)
And I could write case statement in my select to get me what records were matched and what were not.

But the problem is I need the primary ID of each table to insert to the log table (I can't change the business rule of the log table).
So what's the best way to do it then?
If I do group by, I have no way of getting the primary ID"s of each table?

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi, namasi_navaretnam
Thanks for your quick input.
I think that'll do.
Before I close this question, can I ask you one more advice(I will increase the points if you think it's too hard).
I learned today that TransDate on table B does not necessarily be the same as TransDate from table B.

Basically, there is a rule table with following:
RuleID
EmployeeX
DateBuffer
AmountBuffer

So let's say following EmployeeX's 100 dollar example from previoius.
Rule could have following rows
1
X
2
10

2
X
3
5

What this means is that by
Rule 1,
Amount that EmployeeX got paid in table A MAY BE reconciled with amount from table B if table B's transdate lies within 2 days of table A's trans date AND the amount difference between combined total of each table is less than 10 dollars.
Rule 2,
Same as above except date buffer is 3 days and amount buffer is 5 dollars.

So what it means is that if EmployeeX got paid 100 dollars on 12/12/2001 from table A, then it can be reconciled with table B with following data:
table B
--------
1
X
50
12/13/2001

2
X
41
12/14/2001

Then this is reconcilable b/c combined total of table B is 95 dollars which is within 10 dollar buffer and all the dates are within 2 days apart from table A's transdate.

This would fail on rule 2 b/c even though it succeeds on Rule 1, it fails on Rule 2 because it fails on amount buffer which is set to 5.

So in order to reconcile, the sum of table A must adhere to any of the existing rule for that employee before reconciling with table B.

So this invalidates any attempt to group table B based on transdate as they can have buffer based on rule.

Also, complicating the problem is that if table A has 100 amount and table B has five rows of 26 dollars for the same employee X, then table A's 100 dollars should only reconcile with FOUR of those 26 dollars if the amount of buffer was 10 dollar difference. Attmpting to group 5 rows result in failure in reconciling b/c table A is 100 where as table B is 130 dollars. But grouping it by 4 rows would give table A 100 and table B 104 and would succeed if rule's amount buffer was 5 dollars.

I hope you can understand my dilmmea here.
Let me know if you don't want to give advice on this comment and if you don't , then I will close the question and award you the points for your earlier question.

I would be happy give it a try. You may even need a cursor (or even cursor within cursor)to accomplish this. To tell you the truth, it would take more than a day to think and come up with a good solution.

In your design see if you can even use triggers (as you add rows into these tables) to calaculate some useful values that can be used latter.

I will try and do this for one employee and hopefully you can expand it do the same for all employees.

I read through your last comment.
I can't use trigger or anything as I am only getting the data from tableA and tableB as a view.
As for cursor and loop, I dont' mind using either one of'em. I am mostly concerned about the speed.

But I"m afraid you last comment will not work.
Because like I told you, TransDate for tableB is different.
So you can't do group by on tableB and insert into that temporary table.

Also, let's say there are five 24 dollar transactions on TableB for employeeX all within 2 day's time.
And if tableA has 100 dollars, then I would need to match it with FOUR of table B's 24 dollars.
So
table A's 100 is reconciled with table B's 96(24*4) assuming rule allows 2 dollar amount buffer.
And tableB's remaining 24 dollar row would be considred as a failed row.

Hope I"m not being too vague or too pushy.
I just wanted to let you know I really appreciate your help.