smul86 smul86 - 1 month ago 10
SQL Question

Where Clause - Between Condition Error

I'm trying to work on my query below on I have most of it right, but for some reason my BETWEEN condition is not working when trying to filter out the dataset...it doesnt filter it at all. Can anyone take a look at my code below and let me know what I'm doing wrong...or maybe even suggest an alternative way to filter out the records from July to end of September?

SELECT A.[Name] AS ResourceName,
A.[ID] AS ResourceID,
CONVERT(VARCHAR(10),A.[STRT_Date],101) AS StartDate,
CONVERT(VARCHAR(10),A.[END_Date],101) AS EndDate,
CASE WHEN A.[END_Date] > A.[STRT_Date] THEN C2.[DY_OF_CAL_NUM] - C1.[DY_OF_CAL_NUM]
WHEN A.[END_Date] IS NULL and A.[STRT_Date] IS NOT NULL THEN C3.[DY_OF_CAL_NUM] - C1.[DY_OF_CAL_NUM]
WHEN A.[END_Date] = A.[STRT_Date] THEN 1
END AS AgeCalendar
FROM [Report] AS A
INNER JOIN [Dim] AS B
ON A.[ID] = B.[ID]
LEFT JOIN [dbo].[DT_DIM] AS C1
ON CAST(A.STRT_Date AS DATE) = C1.[CAL_DT]
LEFT JOIN [dbo].[DT_DIM] AS C2
ON CAST(A.END_Date AS DATE) = C2.[CAL_DT]
LEFT JOIN [dbo].[DT_DIM] AS C3
ON CAST(GETDATE() AS DATE) = C3.[CAL_DT]
WHERE 1 = 1
AND A.[STRT_Date] BETWEEN '07/01/2016' AND '09/30/2016'
AND A.[Name] like '%S-Info%'
OR A.[Name] like '%S-Dep%'
OR A.[Name] like '%S-Can%'
OR A.[Name] like '%S-Doc%'

Answer

Wrap your Or operators with ():

WHERE A.[STRT_Date] BETWEEN '07/01/2016' AND '09/30/2016'
AND (A.[Name] like '%S-Info%'
     OR A.[Name] like '%S-Dep%'
     OR A.[Name] like '%S-Can%' 
     OR A.[Name] like '%S-Doc%')

What currently happens is that if a record has a Name that is like '%S-Doc%', even if it is not between the dates it will be selected. By wrapping all the variations of the valid Names you say that it has to be between the dates and "one of the following conditions"

Comments