SQL query dilemma

[ree321@gmail.com]

Say you have table with that is filled with the amount of dollars for a
project for given dates.

i.e. 3 columns - Project, Amount, Date

A project could have multiple entries in the tabke but this will vary
depending on the Date

How would you query the top 10 projects in regards to most amount of
dollars where each project has to use the latest date in regards to
amount of dollars.

I tried to use Group By but got stumped as I didn't know how to group
Amount using the Max(Date).

So does anyone know how to achieve this?

[Ed Murphy]

Re: SQL query dilemma

On 21 Aug 2006 23:26:25 -0700, ree321@gmail.co m wrote:

>Say you have table with that is filled with the amount of dollars for a
>project for given dates.
>
>i.e. 3 columns - Project, Amount, Date
>
>A project could have multiple entries in the tabke but this will vary
>depending on the Date
>
>How would you query the top 10 projects in regards to most amount of
>dollars where each project has to use the latest date in regards to
>amount of dollars.
>
>I tried to use Group By but got stumped as I didn't know how to group
>Amount using the Max(Date).
>
>So does anyone know how to achieve this?

create view v1 as
select project, max(date) as max_date
from t
group by project

go

create view v2 as
select t.project, sum(t.amount) as sum_amount
from t join v1 on t.project = v1.project and t.date = v1.max_date
group by t.project

go

select top 10 project
from v2
order by sum_amount

(You can probably collapse some of this using compound queries
if you want.)

[Hugo Kornelis]

Re: SQL query dilemma

On 21 Aug 2006 23:26:25 -0700, ree321@gmail.co m wrote:

>Say you have table with that is filled with the amount of dollars for a
>project for given dates.
>
>i.e. 3 columns - Project, Amount, Date
>
>A project could have multiple entries in the tabke but this will vary
>depending on the Date
>
>How would you query the top 10 projects in regards to most amount of
>dollars where each project has to use the latest date in regards to
>amount of dollars.
>
>I tried to use Group By but got stumped as I didn't know how to group
>Amount using the Max(Date).
>
>So does anyone know how to achieve this?

Hi ree321,

Try if this works:

SELECT TOP 10 a.Project, SUM(a.Amount) AS TotalAmount
FROM YourTable AS a
WHERE a.Date = (SELECT MAX(b.Date)
FROM YourTable AS b
WHERE b.Project = a.Project)
GROUP BY a.Project
ORDER BY TotalAmount DESC

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP

[ree321@gmail.com]

Re: SQL query dilemma

Thanks that worked great when I changed the SUM(a.Amount) to
Max/Min(a.Amount) as I want the latest amount and not the sum of the
Amounts.

Hugo Kornelis wrote:

On 21 Aug 2006 23:26:25 -0700, ree321@gmail.co m wrote:
>
Hi ree321,
>
Try if this works:
>
SELECT TOP 10 a.Project, SUM(a.Amount) AS TotalAmount
FROM YourTable AS a
WHERE a.Date = (SELECT MAX(b.Date)
FROM YourTable AS b
WHERE b.Project = a.Project)
GROUP BY a.Project
ORDER BY TotalAmount DESC
>
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
>
--
Hugo Kornelis, SQL Server MVP

[ree321@gmail.com]

Re: SQL query dilemma

Thanks but haven't tried this out as I went for the simpler solution
below