CODEWITHSUNDEEP
asp.netsql-servervb.net
Andrei Tanasescu
Andrei Tanasescu

Reputation: 25

sql server compare rows in one table

i want to keep track of the changes of a system setup and also to display them in a web application ASP.net VB. the setup information/data is stored in a table like this:

TABLE 1

| SNR | LENSE | CAMERA | SYS |
|-----|-------|--------|-----|
|   1 |  55mm |  rcd30 |   5 | (initial setup sys 5)
|   2 |  80mm |    ph1 |   4 | (initial setup sys 4)
|   3 |  80mm |    ph1 |   4 | (new setup sys 4)
|   4 |  55mm |    ph1 |   5 | (new setup sys 5)
|   5 |  80mm |  rcd30 |   5 | (new setup sys 5)

what i want is to compare for both systems (4 and 5) if and which changes occur between setups of the same system and to create a table where i keep the results and from here to display them every time i load the list of setups.

for example if i compare setup's for the sys nr 5, comparing row 1 and 4 there is a difference on camera; between 4 and 5 both the camera and the lense changes. this works if considering the first record for each system as the initial setup.

CHANGES

| SNR | LENSE | CAMERA | SYS |
|-----|-------|--------|-----|
|   1 |     n |      n |   5 | (initial setup sys 5)
|   2 |     n |      n |   4 | (initial setup sys 4)
|   3 |     n |      n |   4 | (new setup sys 4)
|   4 |     n |      y |   5 | (new setup sys 5)
|   5 |     y |      y |   5 | (new setup sys 5)

is it possible to do this using SQL? any ideas how? SQL has never been my strongest point :(

edit: using SQL server 2012

Upvotes: 1

Views: 85

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

If you're using SQL Server 2012 or newer, you can use LAG to find the previous value, and just use a CASE on whether the old and new value differ;

WITH cte AS (
  SELECT snr, sys,
         lense,  LAG(lense)  OVER (PARTITION BY sys ORDER BY snr) old_lense,
         camera, LAG(camera) OVER (PARTITION BY sys ORDER BY snr) old_camera
  FROM table1
)
SELECT snr, sys,
       CASE WHEN lense  <> old_lense  THEN 'y' ELSE 'n' END lense,
       CASE WHEN camera <> old_camera THEN 'y' ELSE 'n' END camera
FROM cte
ORDER BY snr;

An SQLfiddle to test with.

Upvotes: 2

Related Questions