select hours

Question

select hours

neneco56

Ten Centuries

Points: 1232
More actions
July 21, 2010 at 12:58 pm

#103960

I need to create a select statement (sql 2005) using a datetime field, but the return of that need to be the total every 30 minutes, any ideas???

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would you mind providing a few more details, please?

Read to use format preferred (see the first link in my signature for details). Also, please include your expected result based on your sample data and what you've tried so far.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

neneco56 Ten Centuries Points: 1232 More actions · Answer 2

I need a statement that go over the intire table looking at the datetime field and give me the results like this:

2010-07-11 12:00 total 1.00

2010-07-11 12:30 total 2.00

2010-07-11 13:00 total 1.50

2010-07-11 13:30 total 3.00

2010-07-11 14:00 total 3.50

sum the total field by every 30 minutes, database as 130 thousands records only

thanks

Nelson

LutzM SSC Guru Points: 107049 More actions · Answer 3

Since we don't have a table including sample data, your request to "go over the intire table" simply returns an error.

Like I stated before: please provide DDL and sample data together with your expected result.

Please note that we can't look over your shoulder so we can't see what you see. You need to help us help you.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

neneco56 Ten Centuries Points: 1232 More actions · Answer 4

Its just a normal table

ie.. Totalcost field and createtime field

I need to know every 30 minutes base on createtime the sum(totalcost)

thanks

Nelson

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

I'm sorry, but have you read the article that Lutz has asked you to read?

We are volunteers who give of our own time to help those who need help, in return we need you to help us. By providing us with the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data (as a series of INSERT INTO statements) for the table(s) involved, expected results based on the sample data, and what you have tried so far to solve your problem helps us.

It could be simple thing in your own code, or maybe you are attacking the problem in the wrong direction and need more guidance.

The end result of helping us, is that you will actually get tested code in return for your efforts. The alternative is not much help or simply shots in the dark that may or may not help.

Chad Crawford SSChampion Points: 11610 More actions · Answer 6

I'm going to take a guess and give you the outline for an answer, hopefully this is something close.

Try this code:DATEADD(MINUTE, (DATEDIFF(MINUTE, 0, MyDateField) / 30) * 30, 0)

And group by that field. This will "round down" to the nearest 30 minute mark.

If this isn't what you are looking for, you do need to provide a script that will build a small sample table with example data and an example of the results you want to see. There are a couple other ways to do it too (tally table and DATEADD is one) that might be better suited, depending on what it is you need.

Good Luck!

Chad

DataDog Hall of Fame Points: 3305 More actions · Answer 7

if you truncate the time to nearest 30 minutes (hh:00 or hh:30)

and then group by that:

declare @now datetime

set @now = {ts'2010-01-01 00:59:00'}

selectdateadd(day, datediff(day, 0, @now), 0)

+ dateadd(hour, datepart(hour, @now), 0)

+ dateadd(minute, datepart(minute, @now) / 30 * 30, 0)