Multi-part identifier error in WHERE code SQL

I have this query written to find time gaps in my data. I’m getting “the multi-part identifier can not be bound” errors for code in my where statement (i.e. for xref.Begin_date and animals.Species). Any idea why that is? I thought I specified it in my Inner join select code.

SELECT *,
    DATEDIFF(HOUR, prev_DateYearAndJulian, DateYearAndJulian) AS diff_hours
FROM 
    (SELECT 
         points.OBJECTID, points.CollarSerialNum, points.Latitude, 
         points.Longitude, points.DateYearAndJulian,
         LAG(DateYearAndJulian) OVER (PARTITION BY points.CollarSerialNum ORDER BY points.DateYearAndJulian) AS prev_DateYearAndJulian
     FROM 
         ANIMALS AS animals 
     INNER JOIN 
         ANIMAL_COLLAR_XREF AS xref ON xref.UniqueID = animals.UniqueID 
                                    AND xref.CollarSerialNum = animals.CollarID 
                                    AND xref.Begin_Date = animals.Begin_Date 
     INNER JOIN 
         COLLARS AS points ON points.CollarSerialNum = animals.CollarID) d
WHERE
    (DateYearAndJulian BETWEEN xref.Begin_Date AND COALESCE (xref.End_Date, GETDATE() + 1))
    AND DATEDIFF(HOUR, prev_DateYearAndJulian, DateYearAndJulian) > 13
    AND animals.Species = 'mule deer'

This is my desired output from the collars table with the headers CollarSerialNum, DateYearAndJulian and diff_hours:

040142  2018-01-07 22:00:00.0000000 040142  2018-01-16 00:00:00.0000000 194
040142  2019-12-09 16:00:00.0000000 040142  2019-12-10 18:00:00.0000000 26
040142  2020-01-10 22:00:00.0000000 040142  2020-01-12 04:50:00.0000000 30
040142  2018-08-18 23:00:00.0000000 040142  2018-08-21 03:00:00.0000000 52

Answer

xref is inside a subquery and is not visible in the outer query, which has access only to the columns of d. Either project the Begin_Date and End_Date columns out of the subquery, or push the WHERE clause into the subquery.

Advertisement

Answer