I have been exploring the use of the Geotab Ace AI assistant and the output from the the AI analysing our data has been positive. I was able to generate some well structured outputs, and was wondering if the SQL that is generated as part of the output could be used to create a report or a dashboard item? 

Geotab Ace SQL output to use in report

Hi All, We want to automate getting billing related information for the devices linked to our account in myAdmin. Currently we have to request a report using the billing area in myAdmin, but I was wondering if there is a way to automate this? The scenario:A device is installed and the contract is activated. This in turn then triggers the rate plan to be applied and we can then bill the client for the month.If the device is terminated, the device is still billed for the month that the devices was terminated in.Same when the device is suspended, and the suspended rate plan is applied. Device sharing:According to what I understand, device sharing applies a new contract, thus except for the source device, the target device is also billed for (at a different rate)This means for the time period that the device is shared, there is an additional subscription on the billing report I'm not sure if Geotab applies billing changes in partial (i.e. the device was on a Go plan from 1st of the month and then on the 15th was suspended, then on the 28th terminated), thus applying partial costing for the number of days the device was in a specific rate plan. What I'm looking for is a way to query this historical billing data for our finance department. The expected outcome would be multiple records per contract id during the query period, per billing plan type (to cover when the device changed from say GO to Suspended to Terminated): Input:Start DateEnd DateaccountFilterserialNumberFiltervinFilter Output: Contract IDContract CommentContract Start DateContract End DateContract StatusContract Promo CodeBilling PlanBilling Start DateBilling End DateBilling CostDevice SerialDevice TypeDevice VINDatabase  Any insights would be helpful. 

MyAdmin Billing API

page-user page-user-pieter-coetzer-1882 template-account-profile page-status-200 theme-harmony user-guest

Company

Title

Thx Tucker...  Appreciate the help. Really good to see the community supporting each other!I've also logged a support request with our account manager, but also thought to put it out here so that others may also know. Keep well,Pieter

oh darn... I just noticed that because of the text input limit, you cannot give the supplied query back to ACE to run

Hi Sajad, (I assume... watched the "How to become an Ace Super User" video and well added 1+1 with the comment that you're the PM 😎 ) Thanks for checking in. Looking forward to the evolution of AI integration with the fleet data as a source for LLM based queries. Do you perhaps know if there is a way (almost like the developer API runner interface) where an advanced user/developer/partner could run/execute the queries built by the Ace to validate some of the SQL results?  I know I'm going technical here, but to showcase a discrepancy, I threw this to ACE: The aim is to generate a Carbon Footprint Report per defined city zone for each vehicle, when the vehicle was in that zone. The expected output is a list of entries per vehicle, grouped per day that shows the total time the vehicle was in the zone (for the day) and the estimated CO2 kg calculation (using a mathematical factor value for kg/l). My initial prompt:"Using the Zones from the 'customers' zone group and each trip from 1 January 2025 until 31 January 2025, build an output grouped per zone then day and then by vehicle. For each row add the date in CET formatted yyyy-MM-dd, add the zone name vehicle name, make, model, fuel type, zone name, add the amount of times a vehicle entered a zone, the total amount of time spent in the zone for that day in the format hours:minutes" Following prompt (broken up the query to first build a dataset then expand on it):"Now add to each row the amount of fuel used in the zone in litres during that day, and CO2 emissions in kg. Take into consideration that one litre of diesel creates 2.54kg of CO2 and one litre of petrol will produce 2.31kg of CO2 on average. Finally order the results by zone name, then vehicle name, and finally trip start time in descending order" The query returned looked like this:<code>WITH t_trips_in_zone AS
 (SELECT t_t.TripId
 , t_t.DeviceId
 , t_t.TripStartTime
 , t_t.TripEndTime
 , t_t.StopPointX
 , t_t.StopPointY
 , t_t.IdlingDuration
 , t_z.ZoneId
 , t_z.ZoneName
 FROM `Trip` AS t_t
 JOIN `Zones` AS t_z ON ST_CONTAINS(t_z.Points, ST_GEOGPOINT(t_t.StopPointX, t_t.StopPointY))
 WHERE DATE(t_t.TripStartTime) BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
 , t_daily_fuel AS
 (SELECT t_daily.DeviceId
 , t_daily.UTC_Date
 , t_daily.TotalFuel_Litres
 , t_meta.FuelType
 FROM `VehicleKPI_Daily` AS t_daily
 JOIN `LatestVehicleMetadata` AS t_meta ON t_daily.DeviceId = t_meta.DeviceId
 WHERE t_daily.UTC_Date BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
SELECT t_meta.DeviceName
 , t_fuel.FuelType
 , DATE(t_trips.TripStartTime) AS Date
 , COUNT(t_trips.TripId) AS NumberOfEntries
 , FORMAT_TIMESTAMP('%H:%M', TIMESTAMP_SECONDS(CAST(SUM(t_trips.IdlingDuration) AS INT64))) AS TotalDurationHHMM
 , ROUND(SUM(t_trips.IdlingDuration) / 86400 * SUM(t_fuel.TotalFuel_Litres), 2) AS FuelUsedInZoneLitres
 , ROUND(SUM(t_trips.IdlingDuration) / 86400 * SUM(t_fuel.TotalFuel_Litres) * CASE
 WHEN LOWER(t_fuel.FuelType) = LOWER('Diesel') THEN 2.54
 WHEN LOWER(t_fuel.FuelType) = LOWER('Gasoline') THEN 2.31
 ELSE NULL
 END, 2) AS CO2UsedInZoneKg
 , t_trips.ZoneName
 , t_meta.Manufacturer AS Make
 , t_meta.Model
FROM t_trips_in_zone AS t_trips
JOIN `LatestVehicleMetadata` AS t_meta ON t_trips.DeviceId = t_meta.DeviceId
JOIN t_daily_fuel AS t_fuel ON t_trips.DeviceId = t_fuel.DeviceId
AND DATE(t_trips.TripStartTime) = t_fuel.UTC_Date
GROUP BY 1
 , 2
 , 3
 , 8
 , 9
 , 10
ORDER BY 8
 , 1
 , 3 DESC</code>I found that the calculated fuel values were quite high and my assumption was that there was something up with the grouping and/or summation of the fuel used, so I prompted ACE: "modify the query to return each zone entry as a separate row and add the date and time for the zone entry and exit, while still returning the additional fields and calculations" The output was: <code>WITH t_trips_in_zone AS
 (SELECT t_t.TripId
 , t_t.DeviceId
 , t_t.TripStartTime
 , t_t.TripEndTime
 , t_t.StopPointX
 , t_t.StopPointY
 , t_t.IdlingDuration
 , t_z.ZoneId
 , t_z.ZoneName
 FROM `Trip` AS t_t
 JOIN `Zones` AS t_z ON ST_CONTAINS(t_z.Points, ST_GEOGPOINT(t_t.StopPointX, t_t.StopPointY))
 WHERE DATE(t_t.TripStartTime) BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
 , t_daily_fuel AS
 (SELECT t_daily.DeviceId
 , t_daily.UTC_Date
 , t_daily.TotalFuel_Litres
 , t_meta.FuelType
 FROM `VehicleKPI_Daily` AS t_daily
 JOIN `LatestVehicleMetadata` AS t_meta ON t_daily.DeviceId = t_meta.DeviceId
 WHERE t_daily.UTC_Date BETWEEN DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) )
SELECT t_meta.DeviceName
 , t_fuel.FuelType
 , t_trips.TripStartTime
 , t_trips.TripEndTime
 , FORMAT_TIMESTAMP('%H:%M', TIMESTAMP_SECONDS(CAST(t_trips.IdlingDuration AS INT64))) AS IdlingDurationHHMM
 , ROUND(t_trips.IdlingDuration / 86400 * t_fuel.TotalFuel_Litres, 2) AS FuelUsedInZoneLitres
 , ROUND(t_trips.IdlingDuration / 86400 * t_fuel.TotalFuel_Litres * CASE
 WHEN LOWER(t_fuel.FuelType) = LOWER('Diesel') THEN 2.54
 WHEN LOWER(t_fuel.FuelType) = LOWER('Gasoline') THEN 2.31
 ELSE NULL
 END, 2) AS CO2UsedInZoneKg
 , t_trips.ZoneName
 , t_meta.Manufacturer AS Make
 , t_meta.Model
FROM t_trips_in_zone AS t_trips
JOIN `LatestVehicleMetadata` AS t_meta ON t_trips.DeviceId = t_meta.DeviceId
JOIN t_daily_fuel AS t_fuel ON t_trips.DeviceId = t_fuel.DeviceId
AND DATE(t_trips.TripStartTime) = t_fuel.UTC_Date
ORDER BY 8
 , 1
 , 3 DESC</code>This result was more convincing - When I totalled up the values in the csv output per day, the fuel usage looked to be more correct. I know it's early days, and that is why I like to validate the outputs to make sure the client gets the best data from the system. Any insights would be appreciated. Regards,Pieter

Hey Tucker, Thanks for your reply. Yes, I'm quite familiar with the API/SDK docs, and we have performed some extensive integration with the platform. I use the myAdmin API calls associated to get the Device Contracts with the Rate Plans and loop through these to get the total cost (also putting in validations to ensure the plan is active)<ul><li>When I compare it to the reports generated in the myAdmin billing section, then the values do not match up. I'm also not sure if the Promo Code plays a role in the calculations, but by assumption, I would figure that this was already considered when I look at the rate plans array linked to the device. I also don't know if the billing report applies partial month billing or how it calculates device sharing (the scenarios explained above). </li><li>When I add date ranges to the query, I do not see in the output when a rate plan was applied and when it was stopped (all the dates show a plan start on the beginning of a month with a end date far in the future), even though I know a rate plan was activated on a device on say the 12th of November.</li></ul> I know it's not a clear cut process, but the hope is that someone could explain the process the Billing Report follows to generate the output, as my attempts do not match the outputs using the myAdmin API. Cheers!

I have been exploring the use of the Geotab Ace AI assistant and the output from the the AI analysing our data has been positive. I was able to generate some well structured outputs, and was wondering if the SQL that is generated as part of the output could be used to create a report or a dashboard item?

Hi All, We want to automate getting billing related information for the devices linked to our account in myAdmin. Currently we have to request a report using the billing area in myAdmin, but I was wondering if there is a way to automate this? The scenario:<ul><li>A device is installed and the contract is activated. This in turn then triggers the rate plan to be applied and we can then bill the client for the month.</li><li>If the device is terminated, the device is still billed for the month that the devices was terminated in.</li><li>Same when the device is suspended, and the suspended rate plan is applied.</li></ul> Device sharing:<ul><li>According to what I understand, device sharing applies a new contract, thus except for the source device, the target device is also billed for (at a different rate)</li><li>This means for the time period that the device is shared, there is an additional subscription on the billing report</li></ul> I'm not sure if Geotab applies billing changes in partial (i.e. the device was on a Go plan from 1st of the month and then on the 15th was suspended, then on the 28th terminated), thus applying partial costing for the number of days the device was in a specific rate plan. What I'm looking for is a way to query this historical billing data for our finance department. The expected outcome would be multiple records per contract id during the query period, per billing plan type (to cover when the device changed from say GO to Suspended to Terminated): Input:<ul><li>Start Date</li><li>End Date</li><li>accountFilter</li><li>serialNumberFilter</li><li>vinFilter</li></ul> Output: <ul><li>Contract ID</li><li>Contract Comment</li><li>Contract Start Date</li><li>Contract End Date</li><li>Contract Status</li><li>Contract Promo Code</li><li>Billing Plan</li><li>Billing Start Date</li><li>Billing End Date</li><li>Billing Cost</li><li>Device Serial</li><li>Device Type</li><li>Device VIN</li><li>Database</li></ul>  Any insights would be helpful.

Viimane tegevus

Geotab Ace SQL output to use in report

MyAdmin Billing API