Solved

SQL group by question

Posted on 2003-11-08
6
249 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
  • 4
  • 2
6 Comments
 
LVL 15

Accepted Solution

by:
namasi_navaretnam earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now