Post reply

select hours

  • 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???

  • July 21, 2010 at 2:07 pm

    #1196478

    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]

  • July 21, 2010 at 2:33 pm

    #1196496

    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

  • July 21, 2010 at 2:41 pm

    #1196502

    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]

  • July 21, 2010 at 2:55 pm

    #1196515

    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

  • July 21, 2010 at 3:06 pm

    #1196519

    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.

  • July 21, 2010 at 3:12 pm

    #1196522

    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

  • July 22, 2010 at 1:23 am

    #1196657

    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'}

    select dateadd(day, datediff(day, 0, @now), 0)

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

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

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

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