Using Snowflake I am searching for entries where an individual carried out an action. I search for two identifiers AGENT_NAME and AGENTID and then I use a BETWEEN to search for actions created on that day. If I search for one person the report works perfectly. If I include a second person, the date column breaks and starts displaying all days.
When I run the this it works perfectly.
WHERE
AGENT_NAME = 'John Wick'
AND AGENT_ID = '1234'
AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' AND '2023-01-17 23:59:59.000'
When I try to incorporate multiple people like this, the ACTION_CREATED_DATE column displays results from all times.
WHERE
(AGENT_NAME = 'John Wick' AND AGENT_ID = '1234')
OR (AGENT_NAME = 'Tom Cruise' AND AGENT_ID = '5678')
AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000' AND '2023-01-17 23:59:59.000'
I would prefer to set up 24 different people's AGENT_NAME and AGENT_ID somewhere else within the same query as I do not have permissions to create separate tables/schemas etc, and then in the WHERE statement write their name, calling on the pre-stored data. I tried defining a list of individuals and ID's in a CTE, DECLARE statements, subqueries and temporary tables.
I put everyone in using parentheses and AND/OR in the WHERE condition but testing with two people breaks the BETWEEN function.
It requires additional parenthesis around OR:
WHERE
((AGENT_NAME = 'John Wick' AND AGENT_ID = '1234')
OR (AGENT_NAME = 'Tom Cruise' AND AGENT_ID = '5678'))
AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000'
AND '2023-01-17 23:59:59.000
A more readable way is using IN operator:
WHERE
(AGENT_NAME,AGENT_ID) IN (( 'John Wick', '1234'), ('Tom Cruise','5678'))
AND ACTION_CREATED_DATE BETWEEN '2023-01-17 00:00:00.000'
AND '2023-01-17 23:59:59.000