need query help

curtmorehouse · #1 (**permalink**) 09-27-09, 19:16

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

tonkuma · #2 (**permalink**) 09-27-09, 20:20

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?

curtmorehouse · #3 (**permalink**) 09-28-09, 09:51

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!

tonkuma · #4 (**permalink**) 09-28-09, 10:56

Quote:

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.

Quote:

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

curtmorehouse · #5 (**permalink**) 09-28-09, 11:13

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.

tonkuma · #6 (**permalink**) 09-28-09, 12:10

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
;

curtmorehouse · #7 (**permalink**) 10-07-09, 14:29

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?

tonkuma · #8 (**permalink**) 10-07-09, 14:53

Quote:

that didn't work for me.

What error messages are you received?
What platform and DB2 version/release are you using?

Quote:

I tried it in another very simple query and it fails too.

Please show your tried query and resulting error messages.

tonkuma · #9 (**permalink**) 10-07-09, 15:29

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
;

Internet Services

Web Development

Digital Marketing

Consumer Tech