[RESOLVED] [SQL Server] Aggregates in WHERE clause

[nmadd]

SQL Server 9.0.3068

So, I know I cannot put aggregates in a WHERE clause and this is giving me some trouble with what I think should be a simple query. My goal is to display the sum of sales for items by customer for a certain date range. The date range would be the first sale for the item/customer and all sales up to the following 2 months.

use SalesData;
go
select
    s.CustomerName,
    s.ItemNumber,
    sum(s.GrossAmount) GrossSales,
    min(s.DocumentDate) MinDocDate,
    dateadd(month, 2, min(s.DocumentDate)) MaxDocDate
from dbo.SalesByItem s
where
    s.ItemNumber = '12345'
group by
    s.CustomerName,
    s.ItemNumber
order by
    s.CustomerName

I'd love to do something like where s.DocumentDate between MinDocDate and MaxDocDate but I know I can't. I know I can use aggreagates in the HAVING clause but then I'm forced to group by s.DocumentDate which doesn't work as these are individual days and I want to show the sum of sales for the 2 month period.

Can somebody point this SQL newbie in the right direction?

[si_the_geek]

You can use a sub-query to get a calculated value, eg:

AND s.DocumentDate > (
                      SELECT Min(DocumentDate)
                      FROM dbo.SalesByItem
                      WHERE CustomerName = s.CustomerName
                        AND ItemNumber = s.ItemNumber
                      )

As the main query has an alias for the table, you can refer to it in the sub-query (as I did in the Where clause) to ensure you are mapping to the correct data.

Use a separate sub-query for each value that you want to calculate, and just put it wherever you would normally put a literal value.

SQL Server 9.0.3068

It would be much better to tell us the 'name' of the version (which for 9.x I think is 2005) rather than the full number.

[nmadd]

That seemed to do the trick. Thanks!

And, yes, it is 2005.