Between Date Trouble 1

[tridith]

What I am doing is making a report displaying all the trucks that are inactive for a certian amount of time. The time that we are going to use is selected from a dropdown box(1-24hours)
So, if I pick 3 from the dropdown box, I want to display all the trucks that have been inactive for over 3 hours.

I think I just have to modify the query that I have made from another post. (

http://www.tek-tips.com/viewthread.cfm?qid=1284580&page=1)

I am pretty sure that I will have to use a between, sys_date, and some others, but have no idea where to start.

The query below displays all the trucks in the table, last location. but now i want their last location if the idle time has been over a certian amount of

SELECT 
	QATech_DataStore."cust_trucks"."truck_name", 
	p."date_time", 
	p."location3distance", 
	p."location3" 
FROM 
	QATech_DataStore."cust_positions" p, 
	QATech_DataStore."cust_trucks"  
WHERE 
  p."date_time" = ( 
                      SELECT Max( "date_time") 
                      from QATech_DataStore."cust_positions" p2 
                      WHERE p2."asset_id" = QATech_DataStore."cust_trucks".Oid 
                      AND p2."asset_type_id"=2)
AND
	QATech_DataStore."cust_trucks".OID = p."asset_id"

I think I have to put that entire query as a sub query...??
wow, im confused...lol

Thanks

 

[LKBrwnDBA]

Just add this:

SELECT ...etc...
WHERE p."date_time" = ...etc...
AND SYSDATE - p."date_time" > 3/24
...

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[carp]

How about

SELECT 
    t."truck_name", 
    p."date_time", 
    p."location3distance", 
    p."location3" 
FROM 
    QATech_DataStore."cust_positions" p, 
    QATech_DataStore."cust_trucks" t, 
    (SELECT asset_id, max(date_time) last_time
       FROM QATech_DataStore.cust_positions
      WHERE asset_type = 2
      GROUP BY asset_id) v
WHERE p."date_time" = v.last_time
  AND p.asset_id = v.asset_id
  AND t.OID = p."asset_id"
  AND (SYSDATE - v.last_time)*24 >= <VALUE FROM PICK LIST>;

I haven't tested this, so you may need to tweek it/correct syntax.

 

[tridith]

Both of those above work great, but I was wondering:

Is there any way to have Enterprise Manager Console display how long it takes to run the query and display the results?

If I count i get about 6 seconds for both of those queries to run, what would be the best thing to do to optimize these so that they will run faster?

If I were to add indexes, what would be the ones to add on to?

The QATech_DataStore."cust_positions" table currently has about 165000 records in it(and always rising). the trucks table only has about 15 records in it.

Thanks for the help guys.

 

[carp]

The optimizer uses the columns in the WHERE clause to narrow down the indexes that might be useful. Since the trucks table only has a few rows, any indexes aside from the one on the primary key will probably not do a lot of good. But the cust_positions table could probably make pretty good use of a couple of indexes - at least worth trying!
Given a WHERE clause like
p."date_time" = v.last_time
AND p.asset_id = v.asset_id
AND t.OID = p."asset_id"
I would be trying indexes on the p.date_time column and/or p.asset_id column.

 

[carp]

And also the asset_type column - that could really speed up the inline view ("v").

 

[tridith]

Thanks, I will see how this flys in the office here.

 

[tridith]

OK, another 2 queries, but I will only start with one for now.

Using the same tables as before.

Adding column odometer, <-- Has the current position of the odometer at that time the row was inserted.

My output from the query will be the truck names and the total distance traveled between 2 dates.
My dates will come from a dropdown.

SELECT
    t."truck_name",
    "TotalOdometer"
FROM
    QATech_DataStore."cust_positions" p,
    QATech_DataStore."cust_trucks" t,

WHERE
	 QATECH_DATASTORE."cust_trucks".OID = "p"."asset_id"
and
	 "p"."asset_type_id" = 2
and
	[COLOR=red]("p"."date_time" between to_date('<StartDateFromMyDropdowns>', 'MM/DD/YYYY HH24:MI:SS') and to_date('<EndDateFromMyDropdowns>', 'MM/DD/YYYY HH24:MI:SS'))[/color]

Im not sure about the code in red

I am guessing that I have to add some type of either a join or subquery.

Any questions...let me know.

Thanks.

 

[tridith]

I decided to post the other query too...lol

It will be very close to the one above.

It will return (Truck Name, Date from report, and Distance traveled on the report date)

From the page I will be able to select 2 dates (StartDate, EndDate) with these dates I will display all the trucks total distances for each day between the 2 dates.

The difference between this query and the other one is:
- This query groups the total distances by day.
- The other query totals the distances between the two dates.

Hope this is understandable..lol

Thanks again guys.

carp, I might have to start paying you for your knowledge...lol

 

[carp]

tridith -
This should really be a new post since it's actually a new question.
With that said, I think something like

SELECT t.truck_name, 
       (max(odometer) - min(odometer)) total_odometer
FROM  QATech_DataStore."cust_positions" p,
      QATech_DataStore."cust_trucks" t
WHERE t.oid = p.asset_id
  AND t.oid = truck_of_interest
  AND p.posn_date BETWEEN low_date AND high_date
GROUP BY t.truck_name;

 

[tridith]

worked great carp. Thanks!!

Waat me to start a new post for the 2nd query??

 

[carp]

Sure thing.
That way, we can keep the thread easy to work with and allow future users to focus in on the thread that matches their situation.