Results 1 to 9 of 9

Thread: need query help

  1. #1
    Join Date
    Sep 2008
    Posts
    34

    Unanswered: need query help

    I have a table with info like so...

    truck# Date Time GPSINFO
    100 9/30/9 0:05:43 Omaha,NE
    101 9/30/9 0:23:59 Nowhere,IA
    102 9/30/9 0:24:45 Somewhere,OH
    100 9/30/9 1:06:23 Omaha,NE
    100 9/30/9 1:15:22 Omaha,NE
    101....
    102....
    101...
    100 9/30/9 12:22:23 Somwhere,NE

    What I want to accomplish is a result set only displaying one row per truck, with all the info above, for every xx hours per day (2,4,6,8 ? - this will change with testing). So the query should output the data for each truck#, once per cycle (every 2,4,6,8 ? hours)

    What I am getting now is the truck location at 10:00 and then another one for 10:04, etc.

    I hope this is clear and someone can help. Thanks. PS, my code now is below...

    Code:
    select char(date(uldate),USA), distinct substr(digits(ultime),1,2) as time1, substr(digits(ultime),3,2) as time2,
       ulcity, ulstat, ulunit, ulord#, diodr#, didr1 
      from $(LIBRARY).MCLOCAT, $(LIBRARY).LOAD 
      where ulord#=diodr# and days(current_date) - days(date(ULDATE)) = 1 and  substr(digits(ultime),1,2) = 06 
      or ulord#=diodr# and days(current_date) - days(date(ULDATE)) = 1 and  substr(digits(ultime),1,2) = 10 
      or ulord#=diodr# and days(current_date) - days(date(ULDATE)) = 1 and  substr(digits(ultime),1,2) = 14 
      or ulord#=diodr# and days(current_date) - days(date(ULDATE)) = 1 and  substr(digits(ultime),1,2) = 18 
      or ulord#=diodr# and days(current_date) - days(date(ULDATE)) = 1 and  substr(digits(ultime),1,2) = 22 
      order by ulunit,ultime

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Can you show your required output?

    If there are some values for GPSINFO in a period,
    what value do you want to display for GPSINFO?

    If there was no data for a truck and period,
    do you want to display the row?

    From where did the names ulcity, ulstat, ulunit, ulord#, diodr#, didr1 in your code come?

  3. #3
    Join Date
    Sep 2008
    Posts
    34
    required output would be like so...

    uldate ultime ulunit ulcity ulstat didr1
    9/30/9 4:01:01 101 omaha ne abcd
    9/30/9 4:05:00 102 columbus oh defg
    9/30/9 4:18:00 103 nowhere mi hijk
    9/30/9 6:01:12 101 walcott ia abcd
    9/30/9 6:22:05 102 columbus oh defg
    9/30/9 8:32:22 101 moline il abcd
    9/30/9 8:55:00 102 columbus oh defg

    if there are values for GPSINFO (ulcity,ulstat) for a period, display the first record.

    if there is no data for a time period, don't display anything for that unit.

    I was simplifying my query in my post...
    ulcity = city for GPSINFO
    ulstat = state for GPSINFO
    ulunit = truck#
    ulord# = order # to join mclocat table to load table
    diodr# = order # to join mclocat table to load table
    didr1 = driver code

    thanks!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    required output would be like so...

    uldate ultime ulunit ulcity ulstat didr1
    9/30/9 4:01:01 101 omaha ne abcd
    9/30/9 4:05:00 102 columbus oh defg
    9/30/9 4:18:00 103 nowhere mi hijk
    9/30/9 6:01:12 101 walcott ia abcd
    9/30/9 6:22:05 102 columbus oh defg
    9/30/9 8:32:22 101 moline il abcd
    9/30/9 8:55:00 102 columbus oh defg
    From what input data?

    It must be not from the data in your original post.
    I have a table with info like so...

    truck# Date Time GPSINFO
    100 9/30/9 0:05:43 Omaha,NE
    101 9/30/9 0:23:59 Nowhere,IA
    102 9/30/9 0:24:45 Somewhere,OH
    100 9/30/9 1:06:23 Omaha,NE
    100 9/30/9 1:15:22 Omaha,NE
    101....
    102....
    101...
    100 9/30/9 12:22:23 Somwhere,NE

  5. #5
    Join Date
    Sep 2008
    Posts
    34
    I had simplified my dataset to make posting my question easier

    my dataset is exactly this..

    table1 - mclocat
    ulunit, ulcity, ulstat, uldate, ultime, ulord#

    table2 - load
    diodr#, didr1

    i want to display...

    ulunit, ulcity, ulstat, uldate, ultime, didr1
    where
    ulord# = diodr#
    and
    uldate = yesterday
    and hour(ultime) = 0 or 4 or 8 or 12 or 16 or 20

    but there may be multiple results for each ulunit for each hour and I only want to display the first result for each hour.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    At last, I think that I understood your requirement.

    Please try this:
    (prefixes "t1." and "t2." are used to clarify the source of columns.)
    Updated: Commas were added for SELECT list.
    Code:
    SELECT uldate, ultime, ulunit, ulcity, ulstat
         , didr1
      FROM (SELECT mclocat.*
                 , ROWNUMBER()
                     OVER(PARTITION BY ulunit, HOUR(ultime)
                              ORDER BY ultime) AS rn
              FROM mclocat
             WHERE uldate = CURRENT_DATE - 1 DAY /* Yesterday */
               AND HOUR(ultime) IN (0, 4, 8, 12, 16, 20)
           ) AS t1
      JOIN load t2
       ON  t1.ulord# = t2.diodr#
     WHERE t1.rn = 1
     ORDER BY
           ultime
         , ulunit
    ;
    Last edited by tonkuma; 09-29-09 at 07:58.

  7. #7
    Join Date
    Sep 2008
    Posts
    34

    That doesn't work

    that didn't work for me. I think my system does not allow ROWNUMBER()
    as I tried it in another very simple query and it fails too.

    Any other way of doing it?

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    that didn't work for me.
    What error messages are you received?
    What platform and DB2 version/release are you using?

    I tried it in another very simple query and it fails too.
    Please show your tried query and resulting error messages.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is another examples without using ROWNUMBER():
    Code:
    WITH
     t1 AS (
    SELECT uldate, ultime, ulunit, ulcity, ulstat, ulord#
      FROM mclocat
     WHERE uldate = CURRENT_DATE - 1 DAY  /* Yesterday */
       AND HOUR(ultime) IN (0, 4, 8, 12, 16, 20)
    )
    SELECT CHAR(uldate, USA)  AS uldate
         , ultime, ulunit, ulcity, ulstat
         , ulord#  AS ord#  /* same as diodr# */
         , didr1
      FROM t1
      JOIN load AS t2
       ON  t2.diodr# = t1.ulord#
     WHERE t1.ultime
           = (SELECT MIN(tm.ultime)
                FROM t1 AS tm
               WHERE tm.ulunit       = t1.ulunit
                 AND HOUR(tm.ultime) = HOUR(t1.ultime)
             )
     ORDER BY
           ultime
         , ulunit
    ;
    or

    Code:
    SELECT CHAR(uldate, USA)  AS uldate
         , ultime, ulunit, ulcity, ulstat
         , ulord#  AS ord#  /* same as diodr# */
         , didr1
      FROM (SELECT uldate, ultime, ulunit, ulcity, ulstat, ulord#
              FROM mclocat AS t1
             WHERE uldate = CURRENT_DATE - 1 DAY  /* Yesterday */
               AND HOUR(ultime) IN (0, 4, 8, 12, 16, 20)
               AND ultime
                   = (SELECT MIN(ultime)
                        FROM mclocat AS tm
                       WHERE tm.uldate       = t1.uldate
                         AND tm.ulunit       = t1.ulunit
                         AND HOUR(tm.ultime) = HOUR(t1.ultime)
                     )
           ) AS t1
      JOIN load AS t2
       ON  t2.diodr# = t1.ulord#
     ORDER BY
           ultime
         , ulunit
    ;
    Last edited by tonkuma; 10-07-09 at 16:48.

Posting Permissions

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