Group By Date 1

[tridith]

The query I need, I suspect will be very close to the one below. The query below returns a total of distance traveled between 2 dates for each truck. So if I select 2 dates that span a week, it will return the total distance traveled over the week for each truck.

The new query that I am trying to make will return (Truck Name, Date from report, and Distance traveled on the report date)

So if the dates that I select from a drop down span over 3 days, there will be 3 reports for each truck, so if there are 3 trucks there will be 9 reports.

One of the problems that I am having is grouping on the dates, in the database I am tracking a report to the second, that is giving me trouble for grouping by date.

SELECT 
  QATech_DataStore."cust_trucks"."truck_name", 
  (max("p"."odometer") - min("p"."odometer"))/1000 as "total_odometer" 
FROM 
  QATech_DataStore."cust_positions" "p", 
  QATech_DataStore."cust_trucks" 
WHERE 
  QATech_DataStore."cust_trucks"."OID" = "p"."asset_id" 
AND 
    ("p"."date_time" between to_date('07/18/2006 12:00AM', 'MM/DD/YYYY HH:MIAM') 
  and 
    to_date('07/18/2006 4:30PM', 'MM/DD/YYYY HH:MIAM')) 
AND 
  "p"."asset_type_id" = 2 
GROUP BY 
  QATech_DataStore."cust_trucks"."truck_name"

Thanks.

 

[LKBrwnDBA]

Try:

SELECT...FROM...
 GROUP BY TRUNC(p.date_time)...

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

 

[tridith]

yippi.

Thanks