query by per hour for every week day
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: query by per hour for every week day

  1. #1
    Join Date
    Mar 2001
    Location
    south africa
    Posts
    401

    query by per hour for every week day

    hi
    can any one help me to get the following query PLEASE.If i can get results in little different format ,that's fine too...

    table:Orders
    -----------------
    orderid number,
    orderdate date

    i would like to see the total orders for every by weekday

    Hour Monday Tuesday Wednesday Thursday Friday Saturdary Sunday
    1 20 30 40 20 19 14 15
    2 30 32 40 20 22 19 21
    3 40 34 60 21 25 24 27
    4 50 36 80 22 28 29 33
    5 60 38 100 23 31 34 39
    6 70 40 120 24 34 39 45
    7 80 42 140 25 37 44 51
    8 90 44 160 26 40 49 57
    9 100 46 180 27 43 54 63
    10 110 48 200 28 46 59 69
    11 120 50 220 29 49 64 75
    12 130 52 240 30 52 69 81
    13 140 54 260 31 55 74 87
    14 150 56 280 32 58 79 93
    15 160 58 300 33 61 84 99
    16 170 60 320 34 64 89 105
    17 180 62 340 35 67 94 111
    18 190 64 360 36 70 99 117
    19 200 66 40 37 73 104 123
    20 210 68 40 38 76 109 129
    21 220 70 40 39 79 114 135
    22 230 72 40 40 82 119 141
    23 240 74 40 41 85 124 147
    24 250 76 40 42 88 129 153
    Last edited by prodadmin; 05-26-2006 at 05:13 PM.

  2. #2
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96

    Thumbs up

    Try like this

    SELECT to_char(orderdate,'hh24') hour,
    sum(decode(orderdate,'DAY','MONDAY',1,0)),
    sum(decode(orderdate,'DAY','TUEDAY', 1,0)),
    sum(decode(orderdate,'DAY','WEDDAY',1,0)),
    sum(decode(orderdate,'DAY','THUDAY', 1,0)),
    sum(decode(orderdate,'DAY','FRIDAY',1,0)),
    sum(decode(orderdate,'DAY','SATDAY', 1,0)),
    sum(decode(orderdate,'DAY','SUNDAY', 1,0))
    from orders
    group by to_char(orderdate,'hh24');


    Cheers

    Dilip.

Posting Permissions

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


Click Here to Expand Forum to Full Width