I'm fairly rusty with my SQL game. I have some code (see below) that checks on a database instance (instance001, instance002, etc), then looks for when the LAST_READ and LAST_WRITE fields are null, along with when it was last reset. Nothing too over the top.
The issue I'm having is how to execute this across multiple instances. You'll see I commented out a section. If I add in the appropriate OR statement (
or inst_name like 'instance002'
where inst_name like 'instance001' or inst_name like 'instance002'
declare @timeString nvarchar(50) = CONVERT(varchar(24), GETDATE(), 120)
select DB_NAME, INST_NAME, MIN([LAST_SRVR_RST]) AS MIN_SRVR_RST, LAST_READ, LAST_WRITE, LOG_DATE=@timeString
from CMS_DB_LAST_READ_WRITE -- Targeted DB with data.
where inst_name -- Targeted instance(s)
and LAST_READ is null -- Both read and write must be null
and LAST_WRITE is null -- to show no activity.
--and [LAST_SRVR_RST] = MIN([LAST_SRVR_RST])
group by DB_NAME, INST_NAME, LAST_SRVR_RST, LAST_READ, LAST_WRITE
AND takes precedence over
OR, you need to group the two conditions using parenthesis:
... WHERE (inst_name = 'instance001' OR inst_name = 'instance002') AND LAST_READ IS NULL AND LAST_WRITE IS NULL GROUP BY ...
Note: I changed the usage from
=, as you are looking for an exact match, and it doesn't make much sense to use
LIKE in that situation.
The reason for the grouping is due to the precedence order mentioned above. This order is:
Level Operators 1 ~ (Bitwise NOT) 2 * (Multiply), / (Division), % (Modulo) 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND), ^ (Bitwise Exclusive OR), | (Bitwise OR) 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators) 5 NOT 6 AND 7 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 8 = (Assignment)
What this means is, all of your
AND statements will be evaluated first. After they have all been evaluated, then your
OR statement is evaluated.
This makes your query look more like this to the compiler:
WHERE ( inst_name = 'instance001' AND LAST_READ IS NULL AND LAST_WRITE IS NULL ) OR inst_name = 'instance002'
Which is why you were getting 210 results, as opposed to the expected 61, as it was pulling in all records in
instance002, disregarding your other
Rewriting the query grouping the two conditions as mentioned above would look like this to the compiler instead:
WHERE ( LAST_READ IS NULL AND LAST_WRITE IS NULL ) AND ( inst_name = 'instance002' OR inst_name = 'instance001' )
Which should be the logical conditions you were expecting.
Another alternative way you can write this query without the
OR grouping altogether is by using an
... WHERE inst_name IN ('instance001', 'instance002') AND LAST_READ IS NULL AND LAST_WRITE IS NULL GROUP BY ...