Post reply

calculating period of time

  • March 26, 2013 at 1:37 am

    #270377

    hi, and here comes my next question 🙂

    i need to calculate the sum of quantity by period of days

    from beginning of time till today without writing a sum for each period of days.

    i know i need to use sum and time diff to calculate the quantity but how do i tell the table, give me the first 5 days and then the next 5, etc etc

    i saw something that is called interval (which of course i have no clue how to use) or maybe i should build a loop (which of course i dont know how to do in sql either :w00t:)

    what would you recommend? 🙂

  • March 26, 2013 at 1:41 am

    #1600624

    It is difficult to recommend something based on the description only.

    It would be easier if you can provide some DDL of the tables involved, sample data and the expected results.

    Please check the link in my signature if you not sure how to do this.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • March 26, 2013 at 1:47 am

    #1600625

    ohhh.. here is the table, i need to sum the count (this is part of the table as the data goes forever and ever with the dates...)

    day Count

    2008-01-01 00:00:00.0003

    2008-01-01 11:00:00.0009

    2008-01-01 22:00:00.0003

    2008-01-02 09:00:00.0007

    2008-01-02 20:00:00.0007

    2008-01-03 07:00:00.0004

    2008-01-03 18:00:00.0000

    2008-01-04 05:00:00.0003

    2008-01-04 16:00:00.0006

    2008-01-05 03:00:00.0002

    2008-01-05 14:00:00.0000

    2008-01-06 01:00:00.0001

    2008-01-06 12:00:00.0008

    2008-01-06 23:00:00.0009

    2008-01-07 10:00:00.0000

    2008-01-07 21:00:00.0004

    2008-01-08 08:00:00.0001

    2008-01-08 19:00:00.0008

    2008-01-09 06:00:00.0003

    2008-01-09 17:00:00.0002

    2008-01-10 04:00:00.0003

    2008-01-10 15:00:00.0008

    2008-01-11 02:00:00.0003

    2008-01-11 13:00:00.0004

    2008-01-12 00:00:00.0009

    2008-01-12 11:00:00.0009

    2008-01-12 22:00:00.0002

    2008-01-13 09:00:00.0007

    2008-01-13 20:00:00.0004

    2008-01-14 07:00:00.0001

    2008-01-14 18:00:00.0007

    2008-01-15 05:00:00.0005

    2008-01-15 16:00:00.0005

    2008-01-16 03:00:00.0004

    2008-01-16 14:00:00.0006

    2008-01-17 01:00:00.0009

    2008-01-17 12:00:00.0008

    2008-01-17 23:00:00.0006

    2008-01-18 10:00:00.0007

    2008-01-18 21:00:00.0005

    2008-01-19 08:00:00.0009

    2008-01-19 19:00:00.0007

    2008-01-20 06:00:00.0000

    2008-01-20 17:00:00.0007

    2008-01-21 04:00:00.0009

    2008-01-21 15:00:00.0002

    2008-01-22 02:00:00.0004

    2008-01-22 13:00:00.0007

    2008-01-23 00:00:00.0000

    2008-01-23 11:00:00.0002

    2008-01-23 22:00:00.0009

    2008-01-24 09:00:00.0008

    2008-01-24 20:00:00.0008

    2008-01-25 07:00:00.0007

    2008-01-25 18:00:00.0006

    2008-01-26 05:00:00.0008

  • March 26, 2013 at 1:50 am

    #1600626

    the table looks bad, i am sorry, i need to check the link you gave me :unsure:

    but basically i have dates and a count.

    i need to sum the count of every 5 days

  • March 26, 2013 at 1:51 am

    #1600627

    Can you also provide the expected output based on your sample data?

    That will help to understand your description better.

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • March 26, 2013 at 1:57 am

    #1600628

    it can be something like

    Set Count

    1st set 87

    2nd set 83

    and so on..

  • March 26, 2013 at 2:04 am

    #1600630

    The question is not very clear of what the end result should look like , it will be better if you provide some data and the expected final output.

    Short of writing the query for you, I would suggest you get familiar with the below commands in SQL

    RowNumber

    case

    datepart

    Group by Rollup / Grouping sets

    a combination of the above commands should give you what you need

    Jayanth Kurup[/url]

  • March 26, 2013 at 2:04 am

    #1600631

    Does this help?

    ;WITH MyData ([day], [Count]) AS (

    SELECT '2008-01-01 00:00:00.000', 3 UNION ALL

    SELECT '2008-01-01 11:00:00.000', 9 UNION ALL

    SELECT '2008-01-01 22:00:00.000', 3 UNION ALL

    SELECT '2008-01-02 09:00:00.000', 7 UNION ALL

    SELECT '2008-01-02 20:00:00.000', 7 UNION ALL

    SELECT '2008-01-03 07:00:00.000', 4 UNION ALL

    SELECT '2008-01-03 18:00:00.000', 0 UNION ALL

    SELECT '2008-01-04 05:00:00.000', 3 UNION ALL

    SELECT '2008-01-04 16:00:00.000', 6 UNION ALL

    SELECT '2008-01-05 03:00:00.000', 2 UNION ALL

    SELECT '2008-01-05 14:00:00.000', 0 UNION ALL

    SELECT '2008-01-06 01:00:00.000', 1 UNION ALL

    SELECT '2008-01-06 12:00:00.000', 8 UNION ALL

    SELECT '2008-01-06 23:00:00.000', 9 UNION ALL

    SELECT '2008-01-07 10:00:00.000', 0 UNION ALL

    SELECT '2008-01-07 21:00:00.000', 4 UNION ALL

    SELECT '2008-01-08 08:00:00.000', 1 UNION ALL

    SELECT '2008-01-08 19:00:00.000', 8 UNION ALL

    SELECT '2008-01-09 06:00:00.000', 3 UNION ALL

    SELECT '2008-01-09 17:00:00.000', 2 UNION ALL

    SELECT '2008-01-10 04:00:00.000', 3 UNION ALL

    SELECT '2008-01-10 15:00:00.000', 8 UNION ALL

    SELECT '2008-01-11 02:00:00.000', 3 UNION ALL

    SELECT '2008-01-11 13:00:00.000', 4 UNION ALL

    SELECT '2008-01-12 00:00:00.000', 9 UNION ALL

    SELECT '2008-01-12 11:00:00.000', 9 UNION ALL

    SELECT '2008-01-12 22:00:00.000', 2 UNION ALL

    SELECT '2008-01-13 09:00:00.000', 7 UNION ALL

    SELECT '2008-01-13 20:00:00.000', 4 UNION ALL

    SELECT '2008-01-14 07:00:00.000', 1 UNION ALL

    SELECT '2008-01-14 18:00:00.000', 7 UNION ALL

    SELECT '2008-01-15 05:00:00.000', 5 UNION ALL

    SELECT '2008-01-15 16:00:00.000', 5 UNION ALL

    SELECT '2008-01-16 03:00:00.000', 4 UNION ALL

    SELECT '2008-01-16 14:00:00.000', 6 UNION ALL

    SELECT '2008-01-17 01:00:00.000', 9 UNION ALL

    SELECT '2008-01-17 12:00:00.000', 8 UNION ALL

    SELECT '2008-01-17 23:00:00.000', 6 UNION ALL

    SELECT '2008-01-18 10:00:00.000', 7 UNION ALL

    SELECT '2008-01-18 21:00:00.000', 5 UNION ALL

    SELECT '2008-01-19 08:00:00.000', 9 UNION ALL

    SELECT '2008-01-19 19:00:00.000', 7 UNION ALL

    SELECT '2008-01-20 06:00:00.000', 0 UNION ALL

    SELECT '2008-01-20 17:00:00.000', 7 UNION ALL

    SELECT '2008-01-21 04:00:00.000', 9 UNION ALL

    SELECT '2008-01-21 15:00:00.000', 2 UNION ALL

    SELECT '2008-01-22 02:00:00.000', 4 UNION ALL

    SELECT '2008-01-22 13:00:00.000', 7 UNION ALL

    SELECT '2008-01-23 00:00:00.000', 0 UNION ALL

    SELECT '2008-01-23 11:00:00.000', 2 UNION ALL

    SELECT '2008-01-23 22:00:00.000', 9 UNION ALL

    SELECT '2008-01-24 09:00:00.000', 8 UNION ALL

    SELECT '2008-01-24 20:00:00.000', 8 UNION ALL

    SELECT '2008-01-25 07:00:00.000', 7 UNION ALL

    SELECT '2008-01-25 18:00:00.000', 6 UNION ALL

    SELECT '2008-01-26 05:00:00.000', 8

    )

    SELECT

    [day],

    [Count],

    rn = (ROW_NUMBER() OVER(ORDER BY [day])-1)/5

    FROM MyData

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 26, 2013 at 2:20 am

    #1600641

    Chris that but I need to add each row manually which I didn’t want to do (this is the one thing I know how to do lol.

    Jayanth, I know more or less how to do the first three but probably the one will help is the Grouping sets you mention which I don’t know how to code. But I can look into it.

    I basically want to know how to calculate period of dates (5 amounts of days) from a long list of days.

  • March 26, 2013 at 2:20 am

    #1600642

    I can see you want to group the days into sets of 5 but before you start dont you need an anchor date.. or will this always be the first date returned in the set?

    if it is then this can be done by using the row_number function twice, and partitioning when the first rowcount divides nicely it 5 (not the nicest way but would work) just as i have realised was written by chrisM 🙂 sorry chris didnt read all your script doh.

    the other and by far the easiest would be to create a Dates table like you would for a dates dimension and in there define the date grouping, then its all about joining to this table and grouping by the dategrouping column.

    If any of that makes sense 🙂

  • March 26, 2013 at 2:22 am

    #1600643

    astrid 69000 (3/26/2013)

    Chris that but I need to add each row manually which I didn’t want to do (this is the one thing I know how to do lol.

    Jayanth, I know more or less how to do the first three but probably the one will help is the Grouping sets you mention which I don’t know how to code. But I can look into it.

    I basically want to know how to calculate period of dates (5 amounts of days) from a long list of days.

    You need to add each row manually? What do you mean?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • March 26, 2013 at 2:23 am

    #1600645

    assuming there is a correaltion between the weeknumber and the date and the days in question are weekdays you can also use datepart and then group by week.

    Jayanth Kurup[/url]

  • March 26, 2013 at 2:25 am

    #1600646

    Using, sample data provided by Chris, this is probably what you need

    ;WITH MyData ([day], [Count]) AS (

    SELECT '2008-01-01 00:00:00.000', 3 UNION ALL

    SELECT '2008-01-01 11:00:00.000', 9 UNION ALL

    SELECT '2008-01-01 22:00:00.000', 3 UNION ALL

    SELECT '2008-01-02 09:00:00.000', 7 UNION ALL

    SELECT '2008-01-02 20:00:00.000', 7 UNION ALL

    SELECT '2008-01-03 07:00:00.000', 4 UNION ALL

    SELECT '2008-01-03 18:00:00.000', 0 UNION ALL

    SELECT '2008-01-04 05:00:00.000', 3 UNION ALL

    SELECT '2008-01-04 16:00:00.000', 6 UNION ALL

    SELECT '2008-01-05 03:00:00.000', 2 UNION ALL

    SELECT '2008-01-05 14:00:00.000', 0 UNION ALL

    SELECT '2008-01-06 01:00:00.000', 1 UNION ALL

    SELECT '2008-01-06 12:00:00.000', 8 UNION ALL

    SELECT '2008-01-06 23:00:00.000', 9 UNION ALL

    SELECT '2008-01-07 10:00:00.000', 0 UNION ALL

    SELECT '2008-01-07 21:00:00.000', 4 UNION ALL

    SELECT '2008-01-08 08:00:00.000', 1 UNION ALL

    SELECT '2008-01-08 19:00:00.000', 8 UNION ALL

    SELECT '2008-01-09 06:00:00.000', 3 UNION ALL

    SELECT '2008-01-09 17:00:00.000', 2 UNION ALL

    SELECT '2008-01-10 04:00:00.000', 3 UNION ALL

    SELECT '2008-01-10 15:00:00.000', 8 UNION ALL

    SELECT '2008-01-11 02:00:00.000', 3 UNION ALL

    SELECT '2008-01-11 13:00:00.000', 4 UNION ALL

    SELECT '2008-01-12 00:00:00.000', 9 UNION ALL

    SELECT '2008-01-12 11:00:00.000', 9 UNION ALL

    SELECT '2008-01-12 22:00:00.000', 2 UNION ALL

    SELECT '2008-01-13 09:00:00.000', 7 UNION ALL

    SELECT '2008-01-13 20:00:00.000', 4 UNION ALL

    SELECT '2008-01-14 07:00:00.000', 1 UNION ALL

    SELECT '2008-01-14 18:00:00.000', 7 UNION ALL

    SELECT '2008-01-15 05:00:00.000', 5 UNION ALL

    SELECT '2008-01-15 16:00:00.000', 5 UNION ALL

    SELECT '2008-01-16 03:00:00.000', 4 UNION ALL

    SELECT '2008-01-16 14:00:00.000', 6 UNION ALL

    SELECT '2008-01-17 01:00:00.000', 9 UNION ALL

    SELECT '2008-01-17 12:00:00.000', 8 UNION ALL

    SELECT '2008-01-17 23:00:00.000', 6 UNION ALL

    SELECT '2008-01-18 10:00:00.000', 7 UNION ALL

    SELECT '2008-01-18 21:00:00.000', 5 UNION ALL

    SELECT '2008-01-19 08:00:00.000', 9 UNION ALL

    SELECT '2008-01-19 19:00:00.000', 7 UNION ALL

    SELECT '2008-01-20 06:00:00.000', 0 UNION ALL

    SELECT '2008-01-20 17:00:00.000', 7 UNION ALL

    SELECT '2008-01-21 04:00:00.000', 9 UNION ALL

    SELECT '2008-01-21 15:00:00.000', 2 UNION ALL

    SELECT '2008-01-22 02:00:00.000', 4 UNION ALL

    SELECT '2008-01-22 13:00:00.000', 7 UNION ALL

    SELECT '2008-01-23 00:00:00.000', 0 UNION ALL

    SELECT '2008-01-23 11:00:00.000', 2 UNION ALL

    SELECT '2008-01-23 22:00:00.000', 9 UNION ALL

    SELECT '2008-01-24 09:00:00.000', 8 UNION ALL

    SELECT '2008-01-24 20:00:00.000', 8 UNION ALL

    SELECT '2008-01-25 07:00:00.000', 7 UNION ALL

    SELECT '2008-01-25 18:00:00.000', 6 UNION ALL

    SELECT '2008-01-26 05:00:00.000', 8

    ), cte_Select AS

    (

    SELECT

    [day],

    [Count],

    rn = (DENSE_RANK() OVER(ORDER BY CONVERT(DATE,[day]))-1)/5

    FROM MyData

    )

    SELECTMIN(day) AS FromDay, MAX(day) AS ToDay, SUM(Count) AS Total

    FROMcte_Select

    GROUP BY rn

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • March 26, 2013 at 2:27 am

    #1600647

    but if i create the date table dont i have to add the dates manually?

    to calculate the first period i go

    select sum(Count) from #table where recordday >= '2008-01-01' and recordday < '2008-01-06'

    what i dont want is to write rows from 2008 till today every 5 days.

    that is basically my problem

  • March 26, 2013 at 2:36 am

    #1600650

    as others have posted it depends on what your grouping method is. we can see it has to be 5 day groups,

    * so it that every 5 days from whatever the first date returned is

    * is it by weekday etc

    as for a dates table a quick google search will pull up plenty of scripts for auto populating one, but what it wont have is the definition of how you want to group.

    chris's solution doesnt require you to enter the dates manually, just swap that out for a select from a table and it will work.

Viewing 15 posts - 1 through 15 (of 26 total)

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