Please help me with the SQL query to get the output

Question

Hi Team,

Please find the attached file. Based on the column 'Response_status_code' I need to get the highlighted column 'TrackerID'. Please help me with SQL query how to get that?

Answer 1

The next query tries to correspond to your question:

;
with Q1 as
(
    select *, case when lag(Response_) over (order by Client_Date) <> Response_ then Client_Date end as f
    from MyTable
),
Q2 as
(
    select *, max(f) over (order by Client_Date) as m
    from Q1
)
select Endpoint_, Version_N, Response_, Endpoint_, Client_Date,
    dense_rank() over (order by m) as TrackerID
from Q2
order by Client_Date

It assumes that Client_Date includes distinct time. It is necessary to have a column that can be used for ordering.

Answer 2

Hi @Subhomoy Chakraborty
Please check this query:

;WITH CTE AS  
(  
SELECT *,CASE WHEN Response_status_code = LAG(Response_status_code)OVER(ORDER BY Client_Date)   
		      THEN 0 ELSE 1 END AS TO_SUM    
FROM #Your_table  
)  
SELECT Endpoint_, Version_N,Response_status_code,Endpoint_,Client_Date,SUM(TO_SUM)OVER(ORDER BY Client_Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )AS TrackerID   
FROM CTE  

If there is an incremental primary key column in your table, then I suggest changing the ORDER BY Client_Date to ORDER BY PK_Column in the two over clauses.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.