You can (must) use a Where clause if you refer to soh.OrderDate directly, such as with a Between clause, which would allow you to specify any date range you want.
On 16 July 2013 15:52, Greg Keogh <[email protected]> wrote: > Greg L > > I rejigged my query to follow your sample and it's working. I DID NOT use > the studio query designer, which I think was leading me astray. > > It's just a damn nuisance that you can't use a HAVING on the whole OrderDate > as this makes selection of rows in a range like 2013-05-14 to 2013-07-01 > tricky to compose out of pieces of dates. Is there a nifty trick to simplify > the date range selection? -- Greg K > >> 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, Mark Hurd, B.Sc.(Ma.)(Hons.)
