• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

Visual studio - find maximum dollar amount in a field for each year

I have a table in visual studio that lists nonprofit grant recipients for a number of years.

How can I automatically pull the largest grant for each year.  For example, my results would show Nonprofit2 with the amount for 2006 and Nonprofit5 with the amount for 2007.

I assume it's some kind of command with MAX, but I'm not sure how to write it.

Recipient           Amount        Year
Nonprofit1          $4,000         2006
Nonprofit2          $8,000         2006
Nonprofit3          $2,000         2006
Nonprofit4          $1,000         2007
Nonprofit5          $2,000         2007
0
TinaSC
Asked:
TinaSC
  • 5
  • 4
4 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
whats rthe datatype of Amount ? if it is money  use

select u.*
from yourTable  u
inner join  (selet max(amount) mamount , year from yourTable a group by Year ) as a
on a.mamount = u.amount and a.year  = u.year
0
 
TinaSCAuthor Commented:
 
Thanks.  Amount is money.  

I sort of understand what you have, but Im working with a complicated view to pull the fields and am struggling a bit with how to insert your suggestion.

Recipient (V_GRHistory.RecipientName), Amount (V_GRHistory.AdjustedAmount AS PayAmount) , and Year (YEAR(V_GRHist1.DatePaid) AS Year).  (I probably should have included that the first time).  

Can you help me incorporate your suggestions into this view (code attached)?  

SELECT     
GRCharacteristics.Name, GRCharacteristicCodes.CodeValue, V_GRHistory.RecipientName, V_GRHistory.DatePaid, V_GRHistory.AdjustedAmount AS PayAmount, V_GRHistory.PayStatus, V_GRHistory.FundNumber, YEAR(V_GRHist1.DatePaid) AS Year

FROM         
V_GRHist1  
INNER JOIN GRGrantCharacteristics 
INNER JOIN V_GRHistory ON GRGrantCharacteristics.GrantId = V_GRHistory.GrantId ON V_GRHist1.GrantId = GRGrantCharacteristics.GrantId 
LEFT OUTER JOIN GRCharacteristicCodes ON GRGrantCharacteristics.CharacteristicCodeId = GRCharacteristicCodes.CharacteristicCodeId 
LEFT OUTER JOIN GRCharacteristics ON GRGrantCharacteristics.CharacteristicId = GRCharacteristics.CharacteristicId

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
is GrantId the primary key for V_GRHist1 ?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
TinaSCAuthor Commented:
Yes.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT    
GRCharacteristics.Name, GRCharacteristicCodes.CodeValue, V_GRHistory.RecipientName, V_GRHistory.DatePaid, V_GRHistory.PayStatus, V_GRHistory.FundNumber,
YEAR(V_GRHist1.DatePaid) AS Year, V_GRHistory.AdjustedAmount AS PayAmount
FROM        
V_GRHist1  
INNER JOIN GRGrantCharacteristics
INNER JOIN V_GRHistory ON GRGrantCharacteristics.GrantId = V_GRHistory.GrantId ON V_GRHist1.GrantId = GRGrantCharacteristics.GrantId
LEFT OUTER JOIN GRCharacteristicCodes ON GRGrantCharacteristics.CharacteristicCodeId = GRCharacteristicCodes.CharacteristicCodeId
LEFT OUTER JOIN GRCharacteristics ON GRGrantCharacteristics.CharacteristicId = GRCharacteristics.CharacteristicId
INNER JOIN (SELECT YEAR(V_GRHist1.DatePaid) AS Year, MAX(V_GRHistory.AdjustedAmount)  AS PayAmount
            FROM        
            V_GRHist1  
            INNER JOIN GRGrantCharacteristics
            INNER JOIN V_GRHistory ON GRGrantCharacteristics.GrantId = V_GRHistory.GrantId ON V_GRHist1.GrantId = GRGrantCharacteristics.GrantId
            GROUP BY YEAR(V_GRHist1.DatePaid)
)a  ON a.PayAmount = V_GRHistory.AdjustedAmount and a.Year = YEAR(V_GRHist1.DatePaid)
0
 
TinaSCAuthor Commented:
I ran it & received a timeout error (see attached).

ee-error-122309.doc
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you run it on a new query window, instead of a query designer
0
 
TinaSCAuthor Commented:
Thanks - one last question before I close.  Can you explain why it would work in a new query window and not in query designer?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
some of the features are not supported in the Query designer, also it used to hold the locks for a long time
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now