Hi All, There are 3 tables: 1. Emp(Empid,EmpName) 2. Product - ProdId,ProdName 3. [Sales Trans] (TranId,TranDate,Empid,ProdId,TranAmt) The required query will show the name of all the employees who have made a transaction of more than $50,000 on any particular day.
Here's a new suggestion after your clarification: SELECT DISTINCT e.EmpID, e.EmpName FROM Emp e INNER JOIN [Sales Trans] st ON e.EmpID = st.EmpID GROUP BY e.EmpID, e.EmpName, st.TranDate HAVING SUM(TranAmt) > 50000 Then you'll have one row for each employee that ever had a transactionday of more than 50k. I've ommitted things like TranDate and Products in the result set, since you say you'r only looking for which Employees have transactions of more than 50k for any given day. If you want to include also the transactions and products of that day, it becomes a lot more complex, since you then would want more granular results than your GROUP BY clause. That's also doable, but requires other constructs. ------------------------------ It depends a little on which datatype you have for TranDate. In my example I'm guessing you have SQL Server 2008 and use datatype date. I'm also guessing that TranID is primary key on its own, and that a transaction contains one single row in [Sales trans]. DECLARE @d date SET @d = '2011-10-01' SELECT EmpID, EmpName FROM Emp e INNER JOIN [Sales Trans] st ON e.EmpID = st.EmpID WHERE st.TranAmt>50000 AND TranDate = @d If my assumptions are wrong, I'll get back with another suggestion, when you make clarifications to your question.
select distinct trandate, (Select MAX(empname) from emp e where e.empid=s.empid), sum(tranamt) from [sales trans] s group by trandate,s.empid having SUM(tranamt)>50000 ---it will give you per day per emp per sum(tranAmt on that day) if greater than 50K. you can remove trandate from select if you need just name and amount as we group by trandate here..