T-SQL Aggregate Query

[jendeacott]

Hi,

I have the following SP which I would like to return a total
of the JourneyDuration field for a each day in the search criteria.

I also then want to change the JourneyDuration value from seconds to hours. i.e divide by 60 twice.

Everything works fine until I try and perform the above calculation which still works but does not group by the day.

I'm sure its something small I'm missing but cant see it.

Any help would be great.

Select DISTINCT (DAY(JourneyStart)) as TheDay, Month(JourneyStart) as TheMonth, Year(JourneyStart) as TheYear, 
(CONVERT(numeric(10,1),(SUM(CONVERT(numeric(10,1), JourneyDuration)) /60) /60)) as Hours, MachineID, DataLoadID

From EH_Journey 

where MachineID = @MachineID
AND (JourneyStart >= @StartDate and JourneyStart <= @EndDate)
AND (JourneyEnd <= @EndDate and JourneyEnd >= @StartDate)

group By MachineID, Year(JourneyStart), Month(JourneyStart), DAY(JourneyStart), DataLoadID, JourneyStart
Order By MachineID, Year(JourneyStart), Month(JourneyStart), DAY(JourneyStart)

Web Design Wetherby
Personal Web Design Service

 

[LFCfan]

You're grouping by JourneyStart itself as well as by Day(), Month() etc - could that be it?

group By MachineID, Year(JourneyStart), Month(JourneyStart), DAY(JourneyStart), DataLoadID, JourneyStart

[sub]~LFCfan
who is gnawing on the knowledge[/sub]

 

[jendeacott]

Oh yeah!!!!!

Crazy how you look at something for so long you miss the obvious.

Nice one, Cheers.

Web Design Wetherby
Personal Web Design Service