Professional Web Applications Themes

Quizzical Query - Microsoft SQL / MS SQL Server

I am trying to get a count of how often something happens per hour of the day then data looks similar to: StartTime Duration 2003-07-23 00:00:00.000 2 2003-07-20 00:00:00.000 3 2003-07-24 00:00:00.000 3 2003-07-26 00:30:00.000 5 2003-07-29 00:30:00.000 2 2003-08-01 00:30:00.000 2 2003-08-10 00:30:00.000 2 2003-08-11 00:30:00.000 3 2003-08-12 00:00:00.000 1 2003-08-13 00:00:00.000 1 2003-08-13 00:30:00.000 2 2003-08-14 00:00:00.000 5 2003-07-21 01:00:00.000 2 for this data I would like to have: Hour Count 0 12 1 11 2 6 3 2 4 2 Any ideas? TIA Semper Fi, Red...

  1. #1

    Default Quizzical Query

    I am trying to get a count of how often something happens per hour of the
    day then data looks similar to:



    StartTime Duration

    2003-07-23 00:00:00.000 2

    2003-07-20 00:00:00.000 3

    2003-07-24 00:00:00.000 3

    2003-07-26 00:30:00.000 5

    2003-07-29 00:30:00.000 2

    2003-08-01 00:30:00.000 2

    2003-08-10 00:30:00.000 2

    2003-08-11 00:30:00.000 3

    2003-08-12 00:00:00.000 1

    2003-08-13 00:00:00.000 1

    2003-08-13 00:30:00.000 2

    2003-08-14 00:00:00.000 5

    2003-07-21 01:00:00.000 2



    for this data I would like to have:


    Hour Count

    0 12

    1 11

    2 6

    3 2

    4 2



    Any ideas? TIA



    Semper Fi,

    Red


    Stephen Guest

  2. #2

    Default Re: Quizzical Query

    The below query gives either 12[Count] or 31[Sum]

    I was thinking it would require a cursor and a while loop but was trying to
    avoid the cursor.

    "oj" <com> wrote in message
    news:phx.gbl... [/ref]
    the 
    >
    >[/ref]


    Stephen Guest

  3. #3

    Default Re: Quizzical Query

    Exactly what you want to count/sum?

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Stephen J Bement" <sjbement//AT//sjbdevelopment.com> wrote in message
    news:%phx.gbl... 
    to [/ref]
    > the 
    > >
    > >[/ref]
    >
    >[/ref]


    oj Guest

  4. #4

    Default Re: Quizzical Query

    I want to find the number of times an event happens during each hour of the
    day.

    example
    StartTime Duration
    2003-07-26 00:30:00.000 5

    would have a 'tic mark' in 00:00, 01:00, 02:00, 03:00, and 04:00

    while


    StartTime Duration
    2003-08-13 00:30:00.000 2

    would only show in 00:00 and 01:00
    "oj" <com> wrote in message
    news:%phx.gbl... 
    > to [/ref][/ref]
    of 
    > >
    > >[/ref]
    >
    >[/ref]


    Stephen Guest

  5. #5

    Default Re: Quizzical Query

    Here's the solution...

    --a number table
    select top 100 digit=identity(int,1,1)
    into digits
    from sysobjects,syscolumns

    --the solution
    select datepart(hour,dt) as [hr],count(*) cnt
    from(
    select dateadd(hour,digit-1,starttime) as dt
    from test cross join digits
    where duration >=digit
    )derived
    group by datepart(hour,dt)


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Stephen J Bement" <sjbement//AT//sjbdevelopment.com> wrote in message
    news:phx.gbl... 
    the [/ref][/ref]
    trying [/ref][/ref]
    sum(duration) [/ref][/ref]
    message [/ref]
    > of 
    > >
    > >[/ref]
    >
    >[/ref]


    oj Guest

  6. #6

    Default Re: Quizzical Query

    Here is the whole script...

    create table test(StartTime datetime, Duration int)

    insert test select '2003-07-23 00:00:00.000', 2
    union all select '2003-07-20 00:00:00.000', 3
    union all select '2003-07-24 00:00:00.000', 3
    union all select '2003-07-26 00:30:00.000', 5
    union all select '2003-07-29 00:30:00.000', 2
    union all select '2003-08-01 00:30:00.000', 2
    union all select '2003-08-10 00:30:00.000', 2
    union all select '2003-08-11 00:30:00.000', 3
    union all select '2003-08-12 00:00:00.000', 1
    union all select '2003-08-13 00:00:00.000', 1
    union all select '2003-08-13 00:30:00.000', 2
    union all select '2003-08-14 00:00:00.000', 5
    union all select '2003-07-21 01:00:00.000', 2
    go

    select top 100 digit=identity(int,1,1)
    into digits
    from sysobjects,syscolumns

    select datepart(hour,dt) as [hr],count(*) cnt
    from(
    select dateadd(hour,digit-1,starttime) dt
    from test cross join digits
    where duration >=digit
    )derived
    group by datepart(hour,dt)

    go

    drop table test, digits
    go


    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net




    oj Guest

  7. #7

    Default Re: Quizzical Query

    I cannot figure out what you are doing from the sample data and the lack
    of DDL and specs. However, the usual trick for " count of how often
    something happens per hour of the day " or other peridos of time is to
    build a table of ranges and use a BETWEEN predicate. The skeleton is
    somethign like this:

    SELECT R1.range_name, SUM(F1.foo), COUNT(F1.bar), etc.
    FROM Foobar AS F1, Ranges AS R1
    WHERE F1.event_time BETWEEN R1.start_time AND R1.end_time
    GROUP BY R1.range_name;

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  8. #8

    Default Re: Quizzical Query

    Excellent. Thanx.

    Semper Fi,
    Red
    "oj" <com> wrote in message
    news:phx.gbl... 


    Stephen Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Replies: 1
    Last Post: July 2nd, 09:09 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139