Row Comparison

Question

Hi All,

Fairly new at SQL and can deconstruct some queries however having troubles writing a query for the following result.

I'm trying to collect the (all columns) WHEN a change in Pvalue has occurred.

I cant get lag(PID) to work because the PID can have range of PID's and therefore the column row isn't always sequential

I have managed to create a temp table when PID=X then inner join with lag to show a second PValue column that is one timestamp below. Then using that i can compare the two using a Case when Pvalue 1 does not equal Pvalue 2 then submit this into a temp table and then query that table to where the case is met leaving me with the result i want. However it feels like such a long winded process and bad querying (query at bottom) especially because I'm creating two temp tables each time.

Is someone able to point me in the direction of making it better?

i.e. from the table below i would expect to see

ID--PID--PValue--PTimestamp
2 1 1 datetime2
4 1 0 datetime4
6 1 1 datetime6
9 1 0 datetime9

ID--PID--PValue--PTimestamp
1 1 0 datetime1
2 1 1 datetime2
3 1 1 datetime3
4 1 0 datetime4
5 1 0 datetime5
6 1 1 datetime6
7 1 1 datetime7
8 1 1 datetime8
9 1 0 datetime9

SELECT *
INTO #Temp
FROM
(SELECT a.ID, a.PID, a.Timestamp, a.Pvalue as Row1, LAG(a.Pvalue) OVER(ORDER BY ID) as Row2 from table as a
where PID=1) as x

SELECT *
INTO #Temp2
FROM
(select
CASE WHEN Row1 <> Row2 THEN '1' ELSE '0' END AS Diff, ID as EventID
from #temp) as y

select * from #Temp2 where Diff =1

Answer 1

Check if this usage of LAG works for you:

drop table if exists #Temp

create table #Temp (ID int, PID int, PValue int, PTimestamp varchar(max) )

insert into #Temp values
( 1, 1, 0, 'datetime1' ),
( 2, 1, 1, 'datetime2' ),
( 3, 1, 1, 'datetime3' ),
( 4, 1, 0, 'datetime4' ),
( 5, 1, 0, 'datetime5' ),
( 6, 1, 1, 'datetime6' ),
( 7, 1, 1, 'datetime7' ),
( 8, 1, 1, 'datetime8' ),
( 9, 1, 0, 'datetime9' )


;
with D as
(
    select *, lag(PValue) over (partition by PID order by ID) as PrevPValue
    from #Temp
)
select ID, PID, PValue, PTimestamp
from D
where PValue <> PrevPValue
order by PID, ID

(In this sample timestamp is a string).

Answer 2

Hi @Zygan ,

Please refer below:

drop table if exists [table]  
drop table if exists #temp  
   
 create table [table] (ID int, PID int, PValue int, Timestamp varchar(max) )  
      
 insert into [table] values  
 ( 1, 1, 0, 'datetime1' ),  
 ( 2, 1, 1, 'datetime2' ),  
 ( 3, 1, 1, 'datetime3' ),  
 ( 4, 1, 0, 'datetime4' ),  
 ( 5, 1, 0, 'datetime5' ),  
 ( 6, 1, 1, 'datetime6' ),  
 ( 7, 1, 1, 'datetime7' ),  
 ( 8, 1, 1, 'datetime8' ),  
 ( 9, 1, 0, 'datetime9' )  
      
 SELECT *  
 into #temp  
FROM  
(SELECT a.ID, a.PID, a.Timestamp, a.Pvalue as Row1, lag(a.Pvalue) OVER (ORDER BY ID) as Row2 from [table] as a  
where PID=1) as x  
  
select ID as EventID,pid,Row1 Pvalue, Timestamp  
from #temp  
where Row1<>Row2  

Output:

EventID	pid	Pvalue	Timestamp  
2	1	1	datetime2  
4	1	0	datetime4  
6	1	1	datetime6  
9	1	0	datetime9  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

;with mycte as (
 select *, row_number()over(  order by ID  )-
 row_number()over(partition by PValue order by PTimestamp  ) grp
 from #Temp)
 ,mycte2 as (
  select *,  
 row_number()over(partition by grp order by PTimestamp  ) rn
 from mycte)

 select ID,PID,PValue,PTimestamp
 from mycte2
 WHere grp>0 and rn=1
 order by PTimestamp