SQL Query on single column with a complex where clause

Question

I have a table where I store detailed information about changes to cars (changes to parts).

For example, here is CarChangeHistory table with few changes to parts in several cars (part names are predefined in another table and can never be different from that default list):

CarChangeHistory
CarGUID PartName    PNPNNewValue TimeOfChange
X123    Windshield  344DFSS
X133    Lights      BN23112
X899    Battery     DNNN222
X433    Battery     SM12345

The cars table looks like this:

CarTable
CarGUID Lights Windshield Battery Handle Door Mfg
X123    11111  344DFSS

There are many other similar entries in the CarChangeHistory table.

I want to perform a search and return all items that fall within last 7 days window if following where clause is true:

(Lights LIKE %BN%) AND (Battery = 'DLK222' OR Windshield = true) AND (...) AND (...)

I can convert this to match my table to something like this (this is more logical, pseudo-code. Since it will return nothing if I will use it in SQL query by itself. Since AND conjunction from the above would try to do AND per raw, but I would like to do it per N number of changes/raws for last 7 days):

(PartName = 'Lights' AND PNNewValue LIKE  '%BN%') 
AND  
((PartName = 'Battery' AND PNNewValue =  'DLK222')  OR  (PartName = 'Windshield')) 
AND
(...)
AND
(...)

So.. If I disregard the empty (...), using example table above, it would return:

X123    Windshield  344DFSS
X133    Lights      BN23112

If my example didn't have line with Lights, it would return nothing...

I guess the biggest problem is AND conjunction, how do I treat it in such query? How can I use this where clause to perform such searches?

I have tried the following and it works, but I would need to change where clause (by expanding (Lights LIKE %BN%) AND (Battery = 'DLK222' OR Windshield = true) to (Lights LIKE %BN% OR Windshield = true) OR (Lights LIKE %BN% OR Battery = 'DLK222')). I guess it can become pretty complex if I have even more conditions.

SELECT TimeOfChange, CarGUID 
FROM CarChangeHistory
WHERE (
(((PartName = 'Lights' AND PNNewValue LIKE '%BN%') OR (PartName = 'Battery' AND PNNewValue = 'DLK222')))
OR
(((PartName = 'Lights' AND PNNewValue LIKE '%BN%') OR (PartName = 'Windshield')))
) AND TimeOfChange BETWEEN DATEADD(DAY,-7,GETDATE()) AND GETDATE()
GROUP BY TimeOfChange, CarGUID
HAVING COUNT(*) = 2

Does anyone know better solutions to such problem? What would be best way of converting my logical where clause, which would just return nothing if I run, to something that can actually filter data in table during last 7 days (or whatever is the time window).

This sounds interesting, but I'm really not sure what your question is. Are you asking how to turn your definition of a set (this AND that AND this other thing) into a WHERE clause? — Ann L., May 28 '15 at 13:31
Showing an example of the data (which you have done) and an example of what you want as the result of the query would also help. — Ann L., May 28 '15 at 13:32
I don't get what are you trying to achieve. You have plenty of ands and ors. Can you just describe in words what you want to select? — Giorgi Nakeuri, May 28 '15 at 13:40
I am trying to perform search. Find all parts that where changes in last 7 days based on where statement (with ANDs and ORs). — 22332112, May 28 '15 at 13:41
Do you have related events on different records? If yes, then you have to use EXISTS type clauses to check for related records. — Arvo, May 28 '15 at 13:44
There is Cars table. That have columns for each part: such as Battery, Lights, Windshield... etc — 22332112, May 28 '15 at 13:47
@22332112, best description of your problem would be some test data and desired output. — Giorgi Nakeuri, May 28 '15 at 13:47
Is your question, "How do I dynamically construct a WHERE clause for a varying number of fields?" I mean, most people deal with that [in the application](http://stackoverflow.com/questions/17321281/is-there-a-better-way-to-dynamically-build-an-sql-where-clause-than-by-using-1-1). Is there some reason you can't do this there? — Bacon Bits, May 28 '15 at 14:39

score 1 · Accepted Answer · answered May 28 '15 at 14:25

If I understand correctly, you want to look for changes in your history table. This table doesn't have columns called Windshield, Lights, etc., but a column PartName instead. So for one record you have one PartName. It cannot be 'Windshield' and 'Lights' at the same time. This leaves you with two options:

1) Use EXISTS clauses:

select carguid
from cars c
where exists
(
  select * 
  from carchangehistory cch
  where cch.carguid = c.carguid
  and partname = 'Lights' and pnnewvalue like  '%BN%'
  and timeofchange between dateadd(day,-7,getdate()) and getdate()
)
and exists
(
  select * 
  from carchangehistory cch
  where cch.carguid = c.carguid
  and ((partname = 'Battery' and pnnewvalue =  'DLK222') or (partname = 'Windshield')) 
  and timeofchange between dateadd(day,-7,getdate()) and getdate()
)
and exists
(
  ...
)

This gets you all cars where all the looked up changes were made within the last 7 days. But it queries the same table again and again.

2) Use GROUP BY with a conditional HAVING clause

  select carguid
  from carchangehistory
  where timeofchange between dateadd(day,-7,getdate()) and getdate()
  group by carguid
  having count(case when partname = 'Lights' and pnnewvalue like '%BN%' then 1 end) > 0
  and count(case when (partname = 'Battery' and pnnewvalue =  'DLK222') or partname = 'Windshield' then 1 end) > 0
  and count(case when ... then 1 end) > 0
  ...;

This gives you the same result, but scans your history table just once.

Thanks! Second solution works very well. And I can easier build my where clause using this solution. — 22332112, May 28 '15 at 14:57

SQL Query on single column with a complex where clause

1 Answers1