Derived Table Error: "The multi-part identifier could not be bound"

Question

I'm having trouble getting the results I would like from the query I've built. The overall goal I'm trying to accomplish is to get the first odometer reading of the month and the last odometer reading of the month for a specific vehicle. I would then like to subtract the two to get total miles driven for that month. I figured a derived table with window functions would best help to accomplish this goal (see example SQL below).

SELECT 
    VEHICLE_ID2_FW
FROM 
    (SELECT 
         VEHICLE_ID2_FW,
         LOCATION_CODE_FW, 
         MIN(ODOMETER_FW) OVER(PARTITION BY YEAR(DATE_FW), MONTH(DATE_FW)) AS MIN_ODO,
         MAX(ODOMETER_FW) OVER(PARTITION BY YEAR(DATE_FW), MONTH(DATE_FW)) AS MAX_ODO
     FROM 
         GPS_TRIPS_FW) AS G

I keep running into an issue where the derived table's query, by itself, runs and works. However, when I bracket it in the FROM clause it shoots back an the error

The multi-part identifier could not be bound

Hoping that I could get some help figuring this out and maybe finding an overall better way to accomplish my goal. Thank you!

Tag your question with the database you are using. – Gordon Linoff May 30 '18 at 16:08 — Gordon Linoff, May 30 '18 at 16:08

score 0 · Accepted Answer · answered May 30 '18 at 16:09

Odometers only increase (well, that should be true). So just use aggregation:

select VEHICLE_ID2_FW, year(date_fw), month(date_fw), 
       min(ODOMETER_FW), max(ODOMETER_FW),
       max(ODOMETER_FW) - min(ODOMETER_FW) as miles_driven_in_month
from GPS_TRIPS_FW
group by VEHICLE_ID2_FW, year(date_fw), month(date_fw);

This answers the question that you asked. I don't think it solves your problem, though, because the total miles driven per month will not add up to the total miles driven. The issue are the miles driven between the last record at the end of the month and the first at the beginning of the next month.

If this is an issue, ask another question. Provide sample data, desired results, and an appropriate database tag.

Thanks for the help here! You are correct, I am running into issues between the last record of the month and the first record of the following month; there's a variance between the two. Would you have any insight as to why this is happening and how to correct it? — awilliams8976, May 30 '18 at 17:28

Derived Table Error: "The multi-part identifier could not be bound"

1 Answers1