compare and fetch column detail from same table

Question

I need some help solving the following problem .

table data format

Value       |     MATERIAL                DATE 
2949.00     |   000000000430450051 |  03/11/2013 00:00:00 
2949.00     |   000000000430450051 |  04/15/2013 00:00:00 
2949.00     |   000000000430450051 |  05/13/2013 00:00:00 
2949.00     |   000000000430450051 |  06/24/2013 00:00:00 
2949.00     |   000000000430450051 |  09/03/2013 00:00:00 
2949.00     |   000000000430450051 |  10/14/2013 00:00:00 
2949.00     |   000000000430450051 |  11/11/2013 00:00:00 
2949.00     |   000000000430450051 |  12/16/2013 00:00:00 
2949.00     |   000000000430450051 |  02/24/2014 00:00:00 
2425.26     |   000000000430450051 |  04/21/2014 00:00:00 <===
2425.26     |   000000000430450051 |  05/05/2014 00:00:00 
2425.26     |   000000000430450051 |  08/11/2014 00:00:00 
2425.26     |   000000000430450051 |  09/04/2014 00:00:00 
2425.26     |   000000000430450051 |  10/13/2014 00:00:00 
2425.26     |   000000000430450051 |  12/10/2014 00:00:00 
2425.26     |   000000000430450051 |  02/02/2015 00:00:00 
2425.26     |   000000000430450051 |  03/18/2015 00:00:00 
3032.00     |   000000000430450051 |  04/13/2015 00:00:00 <===
3032.00     |   000000000430450051 |  05/26/2015 00:00:00 
3032.00     |   000000000430450051 |  06/15/2015 00:00:00 
3032.00     |   000000000430450051 |  07/20/2015 00:00:00

How can I achieve like below output, I needs to pick the date column corresponding to change in value column as shown below

2425.26     |     04/21/2014 00:00:00
3032.00     |     04/13/2015 00:00:00

What have you tried so far, have you done any research, what ideas do you have? We're not here to do work for you, but to help you find the answer yourself. [This question](http://stackoverflow.com/questions/5626344/how-to-remove-a-column-from-an-existing-table) might help you remove the centre column, whilst [this](http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select) might help you check if the value has changed. — Ari Cooper-Davis, May 09 '17 at 10:16

score 1 · Answer 1 · answered May 09 '17 at 10:03

You can use lag and get the next value and find change as below

select * from (
select *, NextValue = lag(value,1,null) over (order by date) from #yourMaterial
) a
where value <> nextvalue

Your output:

+---------+--------------------+-------------------------+
|  value  |      material      |          date           |
+---------+--------------------+-------------------------+
| 2425.26 | 000000000430450051 | 2014-04-21 00:00:00.000 |
| 3032    | 000000000430450051 | 2015-04-13 00:00:00.000 |
+---------+--------------------+-------------------------+

score 0 · Answer 2 · answered May 09 '17 at 10:04

0

You can group on the value and material columns and get the MIN date in the select statement

SELECT Value,
Material,
MIN(Date)
FROM [YourTable]
GROUP BY Value,
Material

answered May 09 '17 at 10:04

David Waldron

162
7

score 0 · Answer 3 · answered May 09 '17 at 10:09

This is the complete scenario, using a WITH clause for more readability:

WITH
-- your input
input(value,material,dt) AS (
          SELECT 2949.00,'000000000430450051',DATE '03/11/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '04/15/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '05/13/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '06/24/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '09/03/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '10/14/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '11/11/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '12/16/2013'
UNION ALL SELECT 2949.00,'000000000430450051',DATE '02/24/2014'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '04/21/2014'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '05/05/2014'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '08/11/2014'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '09/04/2014'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '10/13/2014'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '12/10/2014'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '02/02/2015'
UNION ALL SELECT 2425.26,'000000000430450051',DATE '03/18/2015'
UNION ALL SELECT 3032.00,'000000000430450051',DATE '04/13/2015'
UNION ALL SELECT 3032.00,'000000000430450051',DATE '05/26/2015'
UNION ALL SELECT 3032.00,'000000000430450051',DATE '06/15/2015'
UNION ALL SELECT 3032.00,'000000000430450051',DATE '07/20/2015'
)
-- end of your input, start "real" WITH clause - and your query - here
,
input_w_change_marker AS (
  SELECT
    *
  , value <> LAG(value) OVER(PARTITION BY material ORDER BY dt) AS val_has_changed
  FROM input
)
SELECT
  value
, dt
FROM input_w_change_marker
WHERE val_has_changed
;

value   |dt
2,425.26|2014-04-21
3,032.00|2015-04-13

compare and fetch column detail from same table

3 Answers3