1

I gave a query that I want to use to select MachineName, Analyte, TestDate and IdealConcentration for all the percentage diviation > 60 and < -30 and make the -60 be 'Over 60% and -30 be 'Below -30%. and also filtering the data to generate a list for test date within the past 7 days. so far I have the below query but it does not limit the data for the past 7 days 

SELECT MachineName,Analyte,TestDate, IdealConcentration,
    CASE WHEN PercentageDiviation > 60 THEN 'Over 60%'
         WHEN PercentageDiviation < -30 THEN 'Below -30%'
         ELSE 'Good'
    END AS OutsideDiv_Range         
FROM [ReportStagingDB_E].[dbo].[LeveyJenningReport_Negative]
WHERE PercentageDiviation > 60 or PercentageDiviation < -30 
   AND TestDate between dateadd(day,-7,getdate()) and getdate() 
GROUP BY  MachineName,Analyte,PercentageDiviation,TestDate,IdealConcentration.

Thanks

Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
MJay
  • 11
  • 1
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product (and your query uses some non-standard syntax and functions). Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using –  Nov 14 '18 at 16:00

1 Answers1

2

Try putting some parentheses around your WHERE conditions, so that you can be sure they are executing in the right order.

WHERE (PercentageDiviation > 60 or PercentageDiviation < -30)
   AND (TestDate between dateadd(day,-7,getdate()) and getdate())
Robert Harvey
  • 178,213
  • 47
  • 333
  • 501
  • Specifically, `and` has a higher precedence than `or` so the original statement was the equivalent of `WHERE PercentageDiviation > 60 or (PercentageDiviation < -30 AND TestDate between dateadd(day,-7,getdate()) and getdate())`. The parens around `between` are not necessary, `between` has a high precedence. See https://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or – Schwern Nov 14 '18 at 17:04