question

CoolDbGuy avatar image
CoolDbGuy asked

Please Suggest a query for the below Scenario

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.
t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CoolDbGuy avatar image CoolDbGuy commented ·
Thanks for the suggestion but let me clarify more about the question. we need a query to find out the name of all employees who made transaction on any day.But the constraint is it'll show the employee's name if sum of all his transaction crosses 50k for any date. we are not specific on which date. Let me provide some sample data. E1 made trans of 40k for 30th Oct for P1. E1 made trans of 20k for 30th Oct for P2. E1 made trans of 30k for 29th Oct for P1. The query should return E1 as his trans for 30th Oct Crossed 5k.
0 Likes 0 ·
Sri 1 avatar image
Sri 1 answered
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..
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.