group by datetime field

0

Please login or register to vote for this query.

(click on this box to dismiss)

Engineering

Q&A for professionals and students of engineering

declare @T table
(
  CaptureTime DATETIME,
  SnapShotValue INT,
  Id INT
)

insert into @T values
('1 Jan 2012 00:00:00', 100, 1),
('1 Jan 2012 00:00:00', 125, 2),
('1 Jan 2012 00:30:00', 111, 1),
('1 Jan 2012 00:30:00', 129, 2),
('1 Jan 2012 01:00:00', 115, 1),
('1 Jan 2012 01:00:00', 132, 2),
('1 Jan 2012 01:30:00', 113, 1),
('1 Jan 2012 01:30:00', 134, 2),
('1 Jan 2012 02:00:00', 115, 1),
('1 Jan 2012 02:00:00', 138, 2)

SELECT * from @T

select Id, dateadd(hour, datediff(hour, 0, CaptureTime), 0) as [dt],
       sum(SnapShotValue)
from @T
group by Id, dateadd(hour, datediff(hour, 0, CaptureTime), 0)
order by [dt]

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results