Filtering SQL Search Query

Question

How can I filter results in a SQL query?

Let’s say I have Products A-Z, and each product has a monthly observation for X Months, but for most products the value in the observation stays the same for that particular product, but I need to find the products that have observations with a new value, the moment the value in the observation changes. Also, you can have a double to quadruple entry per month if the value doesn’t only change, but coexists, and I need to find every instance that happens for a product.

Example in pic:

enter image description here

Which DBMS are you using? The lag function will help you if you have a DBMS that supports window/analytical functions. — Colin 't Hart, Jun 01 '15 at 13:57
Can you expect only simple changes or double/triple-to-single-and-reverse? Or are their cases were there is both a "simple change" and a change from eg. double to single at the same time? — Colin 't Hart, Jun 01 '15 at 13:59
Please provide us with sample data in the form of create table and insert script rather than just a picture. This will make it much easier for us to start helping you. — Colin 't Hart, Jun 01 '15 at 14:00

Prime03 · Answer 1 · 2015-06-01T15:10:31.853

>Product    Value    Date
>---------- -------- --------
>A          123      Jan-99
>A          123      Feb-99
>A          123      Mar-99
>A          123      Apr-99
>A          123      May-99
>A          456      Jun-99
>E          456      Jan-99
>E          456      Feb-99
>E          654      Apr-99
>E          987      Apr-99
>E          873      Apr-99
>E          987      May-99
>E          456      Jun-99
>Z          852      Jan-99
>Z          852      Feb-99
>Z          852      Mar-99
>Z          963      Apr-99
>Z          852      Apr-99
>Z          852      Jun-99

For this data set I would try and use a function like ROW_NUMBER() to partition the data into blocks that I would like to capture and then filter.

For changing values I would try something like

SELECT *
  FROM( 
    SELECT * , 
           ROW_NUMBER( )OVER( PARTITION BY t.Product , 
                                           t.[Value] ORDER BY t.Product )AS  RowNumber
           FROM dbo.Table_1 t ) AS Result
  WHERE RowNumber = 1;

 Product    Value   Date    RowNumber
 A          123     Jan-99  1
 A          456     Jun-99  1
 E          456     Jan-99  1
 E          654     Apr-99  1
 E          873     Apr-99  1
 E          987     Apr-99  1
 Z          852     Apr-99  1
 Z          963     Apr-99  1

This should return unique values for each product, and then you should be able to quickly see where changes have occurred.

edit: LAG would be another great way to do this as mentioned by Colin.

Filtering SQL Search Query

1 Answers1