Please help me with the SQL query to get the output

Subhomoy Chakraborty 106 Reputation points

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?187891-capture.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,607 questions
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,459 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 106.4K Reputation points

    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.

    1 person found this answer helpful.

  2. LiHong-MSFT 10,031 Reputation points

    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,

    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.