DATEPART, DATEADD or DATEDIFF ???I want data from last year

[Ashley]

I have a query where I want to see the average price of orders in last
year in US or CANADA




AVG((IIf((([Location]="USA") Or ([Location]="CANADA")) And

(( datepart( 'y', ([ORDERDate])) = datepart('y' -1, getadte())


as [AVG COST LAST YEAR],


would I do -1 or what else??????????

 

[John Vinson]

I have a query where I want to see the average price of orders in last
year in US or CANADA




AVG((IIf((([Location]="USA") Or ([Location]="CANADA")) And

(( datepart( 'y', ([ORDERDate])) = datepart('y' -1, getadte())


as [AVG COST LAST YEAR],


would I do -1 or what else??????????

None of the above.

A criterion of

OrderDate >= DateSerial(Year(Date()) - 1, 1, 1) AND OrderDate <
DateSerial(Year(Date()), 1, 1)

will get all records from last year. Use gatadte instead of Date() if
that's a function which gives you a custom date... not sure what it's
intended to do!

If you want to use DatePart, use "yyyy" (the year number) rather than
"y" (the sequential day of the year) in your expression. The
DateSerial() approach will take advantage of any index on ORDERDate,
though, so it's more efficient.

John W. Vinson[MVP]

 

[Guest]

You can restrict the query on USA/Canada locations by means of the IN
operator. A swell as the means of restricting it to last year's data which
John has given you can also use the Year and Date functions like so:

SELECT AVG(Amount) As AverageCostLastYear
FROM Orders
WHERE Location IN ("USA","Canada")
AND YEAR(OrderDate) = YEAR(DATE())-1;

Ken Sheridan
Stafford, England