Hi Greg,
You can put the DATEPART functions in the HAVING (as long as they're in the
GROUP BY). The most common mistake is to try to put column aliases for them
in the HAVING.
USE AdventureWorks2008R2;
GO
SELECT SUM(sod.LineTotal) AS TotalValue,
DATEPART(year,soh.OrderDate) AS OrderYear,
DATEPART(month,soh.OrderDate) AS OrderMonth
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
GROUP BY DATEPART(year,soh.OrderDate),
DATEPART(month,soh.OrderDate)
HAVING DATEPART(year,soh.OrderDate) BETWEEN 2005 AND 2012
ORDER BY OrderYear, OrderMonth;
Regards,
Greg
Dr Greg Low
1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax
SQL Down Under | Web: <http://www.sqldownunder.com/> www.sqldownunder.com
From: [email protected] [mailto:[email protected]]
On Behalf Of Greg Keogh
Sent: Tuesday, 16 July 2013 10:00 AM
To: ozDotNet
Subject: Re: [OT] T-SQL GroupBy and Sum on a DateTime
You can use DATEPART to extract the parts of the date
I tried various combinations like that. I have DATEPART in the select, but
then I get syntax errors in the HAVING because I can't put DATEPARTs into
it.
I can't figure out how to group and sum on "parts" of a DateTime column.
I'll try to provide a tiny sample when I get home this evening.
Greg K
