Filter multiple items with single data range

Question

Hi, very much an SQL novice. I have a select statement for multiple machine terminal numbers AB123456A, AB123457A etc. etc. and want filter these using a single data range i.e. (MyTranDateTime BETWEEN { fn NOW() } -5 AND { fn NOW() }). The problem I face is that the select statement finds all machines but only filters the first one in the list using this date range, all subsequent machine transactions are returned with all dates. See example below.

SELECT *
FROM MyTranTable
WHERE (MyTranNarrative LIKE 'ABC%') AND (MyPersonalTran LIKE '123456_____%') AND (MytranFee = 2.50) AND (MyTranDateTime BETWEEN { fn NOW() } -5 AND { fn NOW() }) AND
(MymachineId = 'AB54321A') OR
(MymachineId = 'AB232323A') OR
(MymachineId = 'AB765436A')

Order By blah blah

My solution was to include the 2.50 value and date range for every machine id in the query, this filters all machines by the correct date range and the results are good. But I was wondering if there is a much more simple approach to filtering multiple items with a singe date range rather than have to include it on every line of the machine ID's?

Thanks for any assistance. Gazza.

Answer 1

Hi @Garry Chapple ,

Welcome to Microsoft Q&A!

Please try with below:

SELECT *  
FROM MyTranTable  
WHERE (MyTranNarrative LIKE 'ABC%') AND (MyPersonalTran LIKE '123456_%') AND (MytranFee = 2.50) AND (MyTranDateTime BETWEEN { fn NOW() } -5 AND { fn NOW() }) AND  
((MymachineId = 'AB54321A') OR  
(MymachineId = 'AB232323A') OR  
(MymachineId = 'AB765436A'))  

OR:

SELECT *  
FROM MyTranTable  
WHERE (MyTranNarrative LIKE 'ABC%') AND (MyPersonalTran LIKE '123456_%') AND (MytranFee = 2.50) AND (MyTranDateTime BETWEEN { fn NOW() } -5 AND { fn NOW() }) AND  
MymachineId in ( 'AB54321A','AB232323A','AB765436A')   

If both of above are not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks for that, option one worked the same as my original statement but option two worked great. I had previously tried placing comma separated machine id's in brackets, but without the "in" function, but it made no difference. However, using "in" was the answer. Thanks heaps! Gazza.