Getting the right grouping

I have the following view statement
SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
NULL THEN
'0' ELSE Orig_miles END AS total_miles
FROM dbo.Engine INNER JOIN
dbo.tblEventInfo ON dbo.Engine.ID =
dbo.tblEventInfo.ID LEFT OUTER JOIN
dbo.orig_miles ON dbo.Engine.EngineNumber =
dbo.orig_miles.EngineNumber
GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
dbo.tblEventInfo.EventDate
HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
(dbo.tblEventInfo.EventDate <
CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
The problem I am having is that when there are more than two records
in the data set that meet the having criteria it spits out two
records. I am looking for it to spit out a single record that is a
sum of all the records available. If I take away the second part of
the having statement it works just fine except without the date
filter(which i need). Any help is greatly appreciated.Plamen responded in .programming.
"pyrahna" <pltaylor3@.gmail.com> wrote in message
news:1180014589.891961.116170@.g4g2000hsf.googlegroups.com...
> I have the following view statement
> SELECT SUM(dbo.tblEventInfo.TotalMiles) + CASE WHEN Orig_miles IS
> NULL THEN
> '0' ELSE Orig_miles END AS total_miles
> FROM dbo.Engine INNER JOIN
> dbo.tblEventInfo ON dbo.Engine.ID =
> dbo.tblEventInfo.ID LEFT OUTER JOIN
> dbo.orig_miles ON dbo.Engine.EngineNumber =
> dbo.orig_miles.EngineNumber
> GROUP BY dbo.Engine.EngineNumber, dbo.orig_miles.Orig_miles,
> dbo.tblEventInfo.EventDate
> HAVING (dbo.Engine.EngineNumber = N'RC-720') AND
> (dbo.tblEventInfo.EventDate <
> CONVERT(DATETIME, '2010-05-31 00:00:00', 102))
> The problem I am having is that when there are more than two records
> in the data set that meet the having criteria it spits out two
> records. I am looking for it to spit out a single record that is a
> sum of all the records available. If I take away the second part of
> the having statement it works just fine except without the date
> filter(which i need). Any help is greatly appreciated.
>