ALZDBA (6/23/2013)

---

the way you did it is grouping by minute ( not taking year/month/day/hour into account )

This is how I would do it.

SELECT

datepart(mi,[startDate]) as MI,

min(startDate) as startDate,

avg(duration) Duration

FROM Logs with (nolock)

WHERE Id IN ( 'HI' )

/* avoid using BETWEEN with datetime datatype */

AND startdate >='2013-06-17 08:30:00.000' AND startdate < '2013-06-17 10:00:00.000'

AND sourcemodule = 'panel'

group by datepart(mi,[startDate])

order by startdate

it depends on the simantics of what you need.

SELECT

dateadd(mi, datediff(mi,[startDate], 0),0) as MI,

min(startDate) as startDate,

avg(duration) Duration

FROM Logs with (nolock)

WHERE Id IN ( 'HI' )

/* avoid using BETWEEN with datetime datatype */

AND startdate >='2013-06-17 08:30:00.000' AND startdate < '2013-06-17 10:00:00.000'

AND sourcemodule = 'panel'

group by dateadd(mi, datediff(mi,[startDate], 0),0) --convert to datetime minute level

order by startdate