I am trying to filter on the year 2022 in my query and it is not filtering on just 2022. I currently have the year filter in the bottom portion of my query I am thinking the where clause needs to be before the joins. I have also tried moving the where clause after the "from tp.load l" and that does not work either. I know that it is that table that I am trying to filter that year on.
select
l.me_id,
l.CREATED_DATETIME,
st.location_name,
st.location_address_1,
st.location_city,
st.location_state_code,
st.location_postal_code,
l.mode_type,
li.weight,
li.hazmat,
li.PRODUCT_DESCRIPTION
from tp.load l
LEFT OUTER JOIN
(Select m.* from
(SELECT ME_ID,hazmat,sum(weight) as 'Weight', product_description,
Row_number() OVER(PARTITION BY ME_ID ORDER BY freight_ID DESC) AS R_NO
FROM [TP].[line_item] where status='active'
GROUP BY ME_ID,HAZMAT, PRODUCT_DESCRIPTION,FREIGHT_ID)
m where R_NO=1)li
ON L.ME_ID=li.me_id
LEFT OUTER JOIN
(Select n.* from
(SELECT ME_ID, location_name, location_address_1, location_city, location_state_code,location_postal_code,
Row_number() OVER(PARTITION BY ME_ID ORDER BY stop_ID DESC) AS R_NO
FROM [TP].[stop] where status='active' and STOP_SEQUENCE_NUMBER = '1') n where R_NO=1) st
ON L.ME_ID=st.me_id
where year(created_datetime)= '2022'
and LOCATION_address_1 LIKE '%6210 GLENWAY%'
or LOCATION_address_1 like '%480 WILEY%'
>Solution :
where year(created_datetime)= '2022'
and LOCATION_address_1 LIKE '%6210 GLENWAY%'
or LOCATION_address_1 like '%480 WILEY%'
is evaluated as
where (year(created_datetime)= '2022' and LOCATION_address_1 LIKE '%6210 GLENWAY%')
or LOCATION_address_1 like '%480 WILEY%'
i.e. either 2022 and GLENWAY, or WILEY (no matter which year.)
But you want
where year(created_datetime)= '2022'
and (LOCATION_address_1 LIKE '%6210 GLENWAY%'
or LOCATION_address_1 like '%480 WILEY%')
I.e. the year must be 2022, and the address LIKE one of GLENWAY or WILEY or etc