Posted - 06/14/2012 : 16:34:01
| I need to write a query that will return a specific number of rows by AssociateNumber. In some cases, an associate will have more then the required number and in some the associate will have less. currently, my query returns all records for all associates. I only want 10 records per associate if they have that many, and if they have less than 10, I want all of them. I tried the use Top 10 and RowCount, but failed. Any assistance is greatly appreciated. Below is what I currently have. I think I need to set up Cases, but a little confused.
"Select a.AssocID, b.tiCenter, b.NameFirst, b.NameLast, a.AcctDate, a.Division, a.AcctNum, a.Jcode, a.Credit
From csAudit.dbo.XSCM189 a
Left Join fwAIMS.dbo.tblAIMSProfile b On a.AssocID = b.AssociateNumber
Where JCode = 909 And AcctDate Between '2012-06-03' And '2012-06-09'
Group By a.AssocID, b.tiCenter, b.NameFirst, b.NameLast, a.AcctDate, a.Division, a.AcctNum, a.JCode, a.Credit
Order By a.AssocID"