Solved
SQL group by question
Posted on 2003-11-08
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?
I would really appreciate if someone could help me out on this.