Group By Hour Range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Group By Hour Range

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-11-13 : 08:02:14

Julio writes "Hi:

I have a table that contains the following fields

tDateTime - datetime
intLine1Weight - int
intLine2Weight - int

the table is populated every min we get a new entry.

Now I am grouping by day and getting the total weight by line with no problem but our production day is from 7:00 AM to 6:59 the next day. How can I group by the time frame.

the select statement I am using now is

select Convert(varchar(8), tdatetime,101) as [Production Day],
Sum(intLine1Weight) as TotalLine1,
Sum(intLine2Weight) as TotalLine2
From tblWeight
Group by Convert(varchar(8), tdatetime,101)
order by Convert(varchar(8), tdatetime,101)

Thanks
Thanks"

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2003-11-13 : 08:27:27

I think the only way to do this is to....adjust the data being reported upon...to offset by 7 hours....I think backwards.

i.e....something recorded at 03:35am should be grouped with the previous days's data....

Investigate doing a dateadd("dd",-7,dayvalue) when reporting, grouping the data.