hii everyone,
can anyone help me with this query
i am trying to add variable to min(create_time) as start_time and max(create_time) as end_time.
here is the query
SELECT tc.vehicle_no as vehicle_no,
datetime_trunc(tc.create_time,minute) as date,
MIN(tc.odometer) as startodometer,
MAX(tc.odometer) as Endodometer,
MAX(tc.odometer) - MIN(odometer) as distance,
MAX(tc.soc) as startsoc,
MIN(tc.soc) as Endsoc,
MAX(tg.lat) as slat,
MIN(tg.lat) as elat,
MAX(tg.lng) as slng,
MIN(tg.lng) as elng
from moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CANas tc
Left join moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS as tg on tg.vehicle_no = tc.vehicle_no and datetime_trunc(tg.create_time,minute) = datetime_trunc(tc.create_time,minute)
where
datetime_trunc(tc.create_time,minute) between datetime_trunc({{start_time}}) and datetime_trunc({{end_time}})
Group by vehicle_no
order by vehicle_no;
I would just do
WHERE {{time}} and use a field filter which is faster and easier Field Filters: create smart filter widgets for SQL questions
can you rectify my query.
SELECT moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.vehicle_no as vehicle_no,
datetime_trunc(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.create_time,minute) as date,
MIN(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.odometer) as startodometer,
MAX(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.odometer) as Endodometer,
MAX(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.odometer) - MIN(odometer) as distance,
MAX(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.soc) as startsoc,
MIN(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.soc) as Endsoc,
MAX(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS.lat) as slat,
MIN(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS.lat) as elat,
MAX(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS.lng) as slng,
MIN(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS.lng) as elng
from moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN
Left join moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS on moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS.vehicle_no = moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.vehicle_no and datetime_trunc(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_GPS.create_time,minute) = datetime_trunc(moeving_prod_db.VIEW_INTELLICAR_TELEMATICS_CAN.create_time,minute)
WHERE {{time}}
group by vehicle_no
order by vehicle_no;
This is a query easily achievable with the GUI builder, I suggest you build it with the query builder rather than SQL, as it will be future-proof as Metabase builds the underlying SQL