Get TimeStamp on value of other columns value changed from 1 to 2

Transact-SQL
1

I am having a Table contain two columns named Time_stamp and PWPH_Pmp_2_FBSts and table name is per_hour_log The Sample of A Screen Shot I embedded
1
for the reference.The Query Which I Wrote is as follows

SELECT MAX(time_stamp) AS recent_datetime
FROM per_hour_log
WHERE PWPH_Pmp_2_FBSts = 2
AND time_stamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
AND EXISTS (
  SELECT 1 FROM per_hour_log
  WHERE PWPH_Pmp_2_FBSts = 1
  AND time_stamp BETWEEN '2023-02-01 01:00:00' AND '2023-02-05 20:00:00'
)

The OutPut I am Expecting is

03-02-2023  05:00:00
3

hi

hope this helps

just to give idea

what if there is more than 1 place where it changes from 1 to 2
take the max date of those ?

create data script

drop table if exists #per_hour_log
create table #per_hour_log ( TmStmp datetime , PWPH_Pmp_2_FBSts int )
insert into #per_hour_log select '2023-02-03 10:00',1
insert into #per_hour_log select '2023-02-03 09:00',1
insert into #per_hour_log select '2023-02-03 08:00',1
insert into #per_hour_log select '2023-02-03 07:00',1
insert into #per_hour_log select '2023-02-03 06:00',2
insert into #per_hour_log select '2023-02-03 05:00',2
insert into #per_hour_log select '2023-02-03 04:00',1
insert into #per_hour_log select '2023-02-03 03:00',1
insert into #per_hour_log select '2023-02-03 02:00',1
insert into #per_hour_log select '2023-02-03 01:00',1
insert into #per_hour_log select '2023-02-03 00:00',1
insert into #per_hour_log select '2023-02-02 23:00',1
insert into #per_hour_log select '2023-02-02 22:00',1
insert into #per_hour_log select '2023-02-02 21:00',1
insert into #per_hour_log select '2023-02-02 20:00',1
insert into #per_hour_log select '2023-02-02 19:00',2

select * from #per_hour_log

; with cte as 
( select lead(PWPH_Pmp_2_FBSts) Over(order by TmStmp desc ) as LeadVal,* from #per_hour_log )
SELECT  
    TmStmp 
FROM 
   cte 
WHERE  
  LeadVal = 1 and  PWPH_Pmp_2_FBSts = 2

image

4

I Tried Running Above Mentioned Query it is giving me output as shown in picture.
Please Let me know is that anything i am doing wrong.

report
report1302×805 23 KB

5

Hi

Are you running it against

My data set

OR

Some other data
Your data set could be having other scenarios

If you provide me your complete data set
I can come up with the solution

I need it in the form of
Create table
Insert data
Script

Thank you

6

It is working against the your dataset but not in mine one i am having SQL table for the same

7

That means

There are other cases

Where it's happening

What do you want to do
Get the max

8 
WITH Boundaries
AS
(
	SELECT TmStmp, PWPH_Pmp_2_FBSts
		,COALESCE(LEAD(TmStmp) OVER (ORDER BY TmStmp), '9999') AS EndTime
		,CASE
			WHEN PWPH_Pmp_2_FBSts = LAG(PWPH_Pmp_2_FBSts) OVER (ORDER BY TmStmp)
			THEN 0
			ELSE 1
		END AS Boundary
	FROM #per_hour_log
)
,Grps
AS
(
	SELECT TmStmp, PWPH_Pmp_2_FBSts, EndTime
		,SUM(Boundary) OVER (ORDER BY TmStmp) AS Grp
	FROM Boundaries
)
SELECT PWPH_Pmp_2_FBSts
	,MIN(TmStmp) AS StartTime
	,MAX(EndTime) AS EndTime
FROM Grps
GROUP BY PWPH_Pmp_2_FBSts, Grp
ORDER BY StartTime;