Row Comparison check and result

Transact-SQL
1

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

2

hi

hope this helps !! :slight_smile:

please click arrow to the left for DROP CREATE Data script 
declare @result table (ID int,PID int,PValue int ,PTimestamp varchar(100))

insert into @result
values
(2, 1, 1, 'datetime2'),
(4, 1, 0, 'datetime4'),
(6, 1, 1, 'datetime6'),
(9, 1, 0, 'datetime9')

select 'Result Expected', * from @result 

declare @t table (ID int,PID int,PValue int ,PTimestamp varchar(100))
insert into @t
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 'Data', * from @t

; with cte as 
(
select case when lag(pvalue) over(order by PID ) <> pvalue then 1 else 0 end as find, * from @t
) 
select * from cte where find = 1

image
image
image

3

Howdy,

This is perfect.
I knew there would be a better way.
I tested it and it works exactly how i wanted.
Thanks for giving me the solution and something new to research.

Cheers.

4

OH .. you like to research !!!

5

what kind of research ..i am curious and interested if you dont mind