Solved

SQL Query to Return a Data Set by Grouping Columns and Determining Max of One Value and Return another Column - SubQuery

Posted on 2015-01-21
15
115 Views
Last Modified: 2015-01-22
I've got a data set where I have Employee ID's with varying Assignment ID's that have monthly pay amounts. I'd like to determine which of the Employee's Assignment ID's had the most Pay Amount per month/year. I've attached an excel example of the data set along with what my desired data set output would be.

From my Data Set I basically want to Determine the Max Amount Value Group By [EmployeeID], [Month], [Year] and then Return whichever AssignmentID is corresponding to that Max Value amount

Something like this:

SELECT Transactions.[EmployeeID], Transactions.[AssignmentID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year];

Open in new window

MaxID-BasedonValue-Example.xlsx
0
Comment
Question by:thomas-sherrouse
  • 9
  • 5
15 Comments
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562601
That is a good start but you can use that as a subquery and join the table again.

Select t1.*,t2.AssignmentID
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year] and t1.MaxOfPayAmount = t2.PayAmount

Open in new window

0
 
LVL 12

Expert Comment

by:FarWest
ID: 40562629
try to use this method

SELECT *
FROM t1 WHERE (id,rev) IN
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)
//
SELECT * from Transactions t1 Where (t1.[EmployeeID], t1.[AssignmentID], t1.[Month], t1.[Year])
IN (
SELECT Transactions.[EmployeeID], Transactions.[AssignmentID], Transactions.[Month], Transactions.[Year],Max(Transactions.[PayAmount])
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year])
0
 

Author Comment

by:thomas-sherrouse
ID: 40562689
OriNetworks - It looks like you're missing a "(" in there.

Here's the Error I get when I run it:

"Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'."
0
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562706
Then the correct syntax should be
Select t1.*,t2.AssignmentID
 FROM
(
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]
) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year] 

Open in new window

0
 

Author Comment

by:thomas-sherrouse
ID: 40562717
It gives me the correct "MaxOfPayAmount" but doesn't identify the correct AssignmentID. It just lists all of the them

EmployeeID	Month	Year	MaxOfPayAmount	AssignmentID
1	1	2014	1000	2222
1	1	2014	1000	3333
1	1	2014	1000	4444
1	2	2014	575	2222
1	2	2014	575	3333
1	2	2014	575	4444
2	1	2014	1250	1111
2	1	2014	1250	5555
2	1	2014	1250	6666
2	2	2014	500	1111
2	2	2014	500	5555
2	2	2014	500	6666
3	1	2014	1250	7777
3	1	2014	1250	9999
3	1	2014	1250	8888
3	2	2014	5000	7777
3	2	2014	5000	9999
3	2	2014	5000	8888

Open in new window

0
 

Author Comment

by:thomas-sherrouse
ID: 40562730
K It looks like the "MaxofPayAmount" Join was left off.

Is this correct?
Select t1.*,t2.AssignmentID
 FROM
(
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]
) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year]  and t1.MaxOfPayAmount = t2.PayAmount

Open in new window

0
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40562745
Oh my, I guess when I copied it for correction it did not come over. My Apologies but yes that should be it although I admit I did not look at your sample attachment
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:thomas-sherrouse
ID: 40562766
Well kudos to you it looks like everything is working! My only concern is the join on the values. That seems like something that could cause an error or two because this value isn't a primary key anywhere and could be duplicated.
0
 
LVL 17

Accepted Solution

by:
OriNetworks earned 500 total points
ID: 40562783
Yes, the values could be duplicated but in the event that the same employee for the same year and month has 2 payment that match the same max amount, how would you handle that anyway? Without knowing more about the application it would be difficult to make that guess. If maybe you want only that latest AssignmentID in case of duplicates you could use:

Select t1.*,MAX(t2.AssignmentID) AS AssignmentID
 FROM
(
SELECT Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year], Max(Transactions.[PayAmount]) AS MaxOfPayAmount
FROM Transactions
GROUP BY Transactions.[EmployeeID], Transactions.[Month], Transactions.[Year]
) t1
left join transactions t2 on t1.EmployeeID=t2.EmployeeID and t1.[Month]=t2.[Month] and  t1.[Year]=t2.[Year]  and t1.MaxOfPayAmount = t2.PayAmount
GROUP BY t1.EmployeeID, t1.Month, t1.Year, t1.MaxOfPayAmount

Open in new window

0
 

Author Comment

by:thomas-sherrouse
ID: 40562813
Perfect solution that would work for me.
0
 

Author Closing Comment

by:thomas-sherrouse
ID: 40562815
Thanks - I've been racking my brain on this.
0
 

Author Comment

by:thomas-sherrouse
ID: 40562874
OriNetworks - I'm having an issue incorporating this into the actual query. My original query that produced the data set consisted of joins to multiple tables so I doesn't seem I can declare the query as "t2" as if it were a standalone table. does that make sense?
0
 

Author Comment

by:thomas-sherrouse
ID: 40562876
I think I figured it out, but please reply if you have a some additional info.
0
 
LVL 17

Expert Comment

by:OriNetworks
ID: 40564975
If you are trying to attach this as a subquery to an additional larger query you may have to wrap it up in another subquery and join it again.
select * from thisIsMainQueryTable inner join
(this is the query in my answer) t3 on ....

Otherwise if your existing query already uses t2 as an alias for a different table you can just change all the references to t2 in the answer to something else.
0
 

Author Comment

by:thomas-sherrouse
ID: 40565466
Yes that is what I did. I replaced my original subquery wherever the "Transactions" Table was referenced.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Optimizing a query 3 33
Get Duration of last Status Update 4 29
Sql Join Problem 2 22
SQL Server merge records in one table 2 10
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

932 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

13 Experts available now in Live!

Get 1:1 Help Now