Working a new SQL query and coming up with an Invalid column and not sure why this is.
DECLARE @start_date date
DECLARE @end_date date
SET @start_date = '01-01-2020'
SET @end_date = '12-31-2020'
SELECT
s.STATE,
DATE
MILEAGE
FROM tbSTATES s
LEFT OUTER JOIN (
SELECT
STATE,
DISPATCH_DATE AS DATE,
SUM(MILEAGE) AS MILEAGE
FROM Fuel_Table f
CROSS APPLY ( VALUES
(UL_STATE_1, MILEAGE_S1),
(UL_STATE_2, MILEAGE_S2),
(UL_STATE_3, MILEAGE_S3),
(UL_STATE_4, MILEAGE_S4),
(UL_STATE_5, MILEAGE_S5),
(UL_STATE_6, MILEAGE_S6),
(UL_STATE_7, MILEAGE_S7),
(UL_STATE_8, MILEAGE_S8)
) AS ca1 (STATE, MILEAGE)
WHERE STATE LIKE '[A-Z]%' AND DATE BETWEEN @start_date AND @end_date
GROUP BY STATE
) AS f ON f.STATE = s.ABBREVIATION
ORDER BY s.STATE
The error shows in the WHERE
clause that DATE
is an invalid column. Can someone advise where my error is or why DATE
is considered an invalid column please? FYI: @start_date
& @end_date
will be parameters passed but hard coded for testing purposes.