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
114 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

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!

 

Author Comment

by:thomas-sherrouse
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect solution that would work for me.
0
 

Author Closing Comment

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

Author Comment

by:thomas-sherrouse
Comment Utility
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
Comment Utility
I think I figured it out, but please reply if you have a some additional info.
0
 
LVL 17

Expert Comment

by:OriNetworks
Comment Utility
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
Comment Utility
Yes that is what I did. I replaced my original subquery wherever the "Transactions" Table was referenced.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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…

771 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

7 Experts available now in Live!

Get 1:1 Help Now