?
Solved

SQL group by question

Posted on 2003-11-08
6
Medium Priority
?
256 Views
Last Modified: 2008-02-07
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.

0
Comment
Question by:StriderX
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 1000 total points
ID: 9709888
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
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9709912
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

0
 

Author Comment

by:StriderX
ID: 9710864
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9711121
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




0
 

Author Comment

by:StriderX
ID: 9715876
Hi,namasi_navaretnam
I really appreciate your help.

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.

0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9716559
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.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question