# 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?

I would really appreciate if someone could help me out on this.

###### Who is Participating?
I wear a lot of hats...

"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.

Commented:
This query may help

select ta.Id
from TableA ta,
(select A.EmpCode, A.TransDate
from (SELECT EmpCode, TransDate , SUM(Amt) as sumamt
from TableA
Group By EmpCode, TransDate) as A,
(SELECT EmpCode, TransDate , SUM(Amt) as sumamt
from TableA
Group By EmpCode, TransDate) as B
WHERE A.EmpCode = B.EmpCode AND
A.TransDate = B.TransDate AND
A.sumamt = B.sumamt) as C
where ta.EmpCode = C.EmpCode and
ta.TransDate = C.TransDate
union all
select tb.Id
from TableB tb,
(select A.EmpCode, A.TransDate
from (SELECT EmpCode, TransDate , SUM(Amt) as sumamt
from TableA
Group By EmpCode, TransDate) as A,
(SELECT EmpCode, TransDate , SUM(Amt) as sumamt
from TableA
Group By EmpCode, TransDate) as B
WHERE A.EmpCode = B.EmpCode AND
A.TransDate = B.TransDate AND
A.sumamt = B.sumamt) as C
where tb.EmpCode = C.EmpCode and
tb.TransDate = C.TransDate

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
This proc may also help. I assume in both cases (straight sql, sp) TransDate just contains the date, not time.

create procedure EmployeeSalary
as
BEGIN

CREATE #TableA
(
EmpCode    int NULL,
TransDate  datetime NULL,
SumAmt     numeric(15,5) NULL
)

CREATE #TableB
(
EmpCode    int NULL,
TransDate  datetime NULL,
SumAmt     numeric(15,5) NULL
)

/* I assume that Trans date does not have a time, just date*/

INTO #TableA
SELECT EmpCode, TransDate , SUM(Amt)
from TableA
Group By EmpCode, TransDate

/* I assume that Trans date does not have a time, just date*/

INTO #TableB
SELECT EmpCode, TransDate , SUM(Amt)
from TableB
Group By EmpCode, TransDate

SELECT ta.ID
FROM TableA ta
(select A.EmpCode, A.TransDate
FROM #TableA A, #TableB B
WHERE A.EmpCode = B.EmpCode and
A.TransDate = B.TransDate and
A.SumAmt = B.SumAmt ) as C
WHERE ta.EmpCode = C.EmpCode and
ta.TransDate = C.TransDate
UNION ALL
SELECT tb.ID
FROM TableB tb
(select A.EmpCode, A.TransDate
FROM #TableA A, #TableB B
WHERE A.EmpCode = B.EmpCode and
A.TransDate = B.TransDate and
A.SumAmt = B.SumAmt ) as C
WHERE tb.EmpCode = C.EmpCode and
tb.TransDate = C.TransDate

DROP TABLE #TableA
DROP TABLE #TableB

END

Author Commented:
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.

Thanks for your help.
I really appreciate it.
Commented:
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.

CREATE #TableA
( EmpCode varchar(50),
Amt numeric(25,5),
TransDate datetime,
RuleStatisfiedFlg char(1) Default 'N'
)

CREATE #TableB
( EmpCode varchar(50),
Amt numeric(25,5),
TransDate datetime
)

INSERT into #TableA
SELECT EmpCode, Amt, TransDate, 'N'
from TableA
where EmpCode = 'AAA'

INSERT into #TableB
SELECT EmpCode, TransDate, Sum(Amt)
from TableB
where EmpCode = 'AAA'
Group By EmpCode, TransDate

// you will need to loop thru to see if all rules are satisfied.
//Check For Rule1

select @amtbuffer = AmtBuffer, @DateBuffer = DateBuffer
from RuleTable
WHERE EmpCode = 'AAA' and RuleId = @RuleId

//Loop all rows in TableA and set RuleSatisfiedFlg to Y/N based on the values in #TableB for each empployee

Update #TableA
Set RuleStatisfiedFlg  = 'Y'
FROM #TableA a, #TableB b
WHERE a.EmpCode = b.EmpCode and
DATEDIFF(d, a.TransDate, b.TransDate) = @DateBuffer and
a.amt - b.amt >= 0

If i have sometime I will revisit this question.

HTH

Namasi

Author Commented:
Hi,namasi_navaretnam
I really appreciate your help.

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.

Commented:
StriderX,

I think if you close this issues and start a new one it will get  attention from other experts. Usually experts pick a question to answer based on the original question.

So, you may get a lots of feedback if you close this one and start a new one. In this case original question is answered but the pending questions does not get the attention it requires.

HTH

Namasi.
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.