Yeah, the code's a bit obscure perhaps, but it's the most efficient way to do this.
It uses a HAVING statement. As we know, a WHERE clause tests on a single row; in contrast, a HAVING clause tests only the final result of a group of rows. The final tests are based on the columns in the GROUP BY in the query, in this case:
GROUP BY DBName, TableName
The code was:
"HAVING MAX(CASE WHEN C.ColumnName Like ('%DATE%') THEN 1 ELSE 0 END) = 1 AND"
"MAX(CASE WHEN C.ColumnName IN ('QuoteID') THEN 1 ELSE 0 END) = 0"
Each row gets evaluated using the CASE, but the final test of the value (= 1 / = 0) is not done until the entire group of rows has been processed.
So, let's walk thru some sample data rows:
DBName TableName ColumnName
db1 table1 date1
Will return a value of 1 for the first HAVING CASE (the LIKE is true).
Will return a value of 0 for the second HAVING CASE (the IN is false).
db1 table1 column2
Will return a value of 0 for the first HAVING CASE (the LIKE is not true). But, notice that the HAVING is doing a MAX of the value. Since a 1 has already been found, the value will remain at 1.
Will return a value of 0 for the second HAVING CASE (the IN is false). The value remains at 0.
Say there are no more rows for those db1, table1 values. The first value is 1, the second value is 0; both conditions are true and the group will be included in the results.
db1 table2 date1
same as above, 1 / 0
db1 table2 column2
same as above, 0 / 0 for the row, still 1 / 0 for the group
db1 table2 QuoteID
Will return 0 for the first HAVING but 1 for the second HAVING. Since the second HAVING is also a MAX, the group values are now:
1 / 1
db1 table2 someothercolumn
Will return 0 and 0 for the row values, but the max group values are still 1 / 1.
End of rows for ( db1, table2 ).
This group will NOT be selected because the second value is 1 and not 0, so the AND condition fails.