Very Computer

I'm continuing to work on subtotals by date range.  David Portas
yesterday prodided me with the following, which is great way to do
subtotals by month!  

<<SELECT
DATENAME(MONTH,MIN(fldEventTime))+STR(YEAR(min(eventTime)),5),count(*),
fldEventTime
FROM tblAuditLog
GROUP BY YEAR(fldEventTime),MONTH(fldEventTime),eventstring
having fldEventString='user created'
ORDER BY YEAR(fldEventTime),MONTH(fldEventTime)>>

But, I need to be able to add a date range.  However, adding
fldEventTime to the HAVING clause creates the error...
'fldEventTime' is invalid in the HAVING clause because it is not
contained in either an aggregate function or the GROUP BY clause'.  This
makes sense.  My normal solution to this would be to use another select
statement as the basis of my recordset.

SELECT
DATENAME(MONTH,MIN(fldEventTime))+STR(YEAR(min(fldEventTime)),5),count(*
), fldEventTime
FROM (select * from tblAuditLog where eventTime <=getdate()) rs
GROUP BY YEAR(fldEventTime),MONTH(fldEventTime),fldeventstring

having fldEventString='user created'
ORDER BY YEAR(fldEventTime),MONTH(fldEventTime)

My question is, 'is there a shorter/better solution than this'?

Thanks to all.

Robert

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I think WHERE is what you are looking for, not HAVING.

SELECT DATENAME(MONTH,MIN(eventtime))+STR(YEAR(MIN(eventTime)),5),
 COUNT(*), MIN(eventtime)
 FROM tblAuditLog
 WHERE eventtime <= CURRENT_TIMESTAMP
  AND eventstring='user created'
 GROUP BY YEAR(eventtime), MONTH(eventtime)
 ORDER BY YEAR(eventtime), MONTH(eventtime)

--
David Portas
------------
Please reply only to the newsgroup
--