Populate Datas by Hour or by Minutes

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

General SQL Server Forums

New to SQL Server Programming

Populate Datas by Hour or by Minutes

Author

Topic

CarloCarlo
Starting Member

2 Posts

Posted - 2014-09-08 : 23:09:59

Good Day everyone,

i have a table that populates the data every second.
ex.
10:40:10
10:40:09
10:40:08
10:40:07
... so on

the problem is i want to gather the data like
Every Hour

10:00:00
09:00:00
08:00:00
07:00:00
..and so on

and Every Minutes like (1 Min, 5 mins, or 10 Mins, 30 Mins)

10:40:00
10:30:00
10:20:00
10:10:00
.. so on

by this, it will make my reporting not over populated.

Im using SQL server 2008 R2

hope to get quick reply..thank you.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-09 : 08:20:33

You can get what you want using GROUP BY ROLLUP

so:


ROLLUP (DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))

see here for more: http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

CarloCarlo
Starting Member

2 Posts

Posted - 2014-09-12 : 02:45:32

hi,

this is how i enter your given code.

SELECT TOP 1000 [TagName]
,[TagIndex]
,[TagType]
,[TagDataType]
,[DateAndTime]
,[Millitm]
,[Val]
,[Status]
,[Marker]
FROM [database].[dbo].[table]

order by
ROLLUP (DATEPART(yyyy,[DateAndTime]))
,(DATEPART(mm,[DateAndTime]))
,(DATEPART(dd,[DateAndTime]))

sql says: Msg 195, Level 15, State 10, Line 14
'ROLLUP' is not a recognized built-in function name.

how should i enter that "ROLLUP" command?

thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-12 : 08:02:38

Did you read up on GROUP BY ROLLUP? http://msdn.microsoft.com/en-ca/library/ms177673.aspx

You want GROUP BY ROLLUP (as I posted), not ORDER BY ROLLUP (which is invalid)

Make sure you read the documentation a few times before proceding

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources