Query to find data where value of data changed to 0



  • I am trying to write a query to find the rows where value of particular column changes to 0 from some other value say 1 or above. The value may change from 0 to other value again. But we need data for only the rows where it was some value and where it changed to 0.

    Suppose we have a table with below data:

    user_id Event_time Value
    1 Dec 23 2021 8:04AM 1
    1 Dec 23 2021 8:05AM 5
    1 Dec 23 2021 8:06AM 0
    1 Dec 23 2021 9:00AM 1
    2 Dec 23 2021 8:05AM 0
    2 Dec 23 2021 9:06AM 7
    2 Dec 23 2021 10:04AM 1
    3 Dec 23 2021 8:05AM 0
    4 Dec 23 2021 9:06AM 1
    4 Dec 23 2021 10:04AM 0

    So the output should be like this:

    user_id Event_time Value
    1 Dec 23 2021 8:05AM 5
    1 Dec 23 2021 8:06AM 0
    4 Dec 23 2021 9:06AM 1
    4 Dec 23 2021 10:04AM 0

    In short i need data for the rows when value is 1 and changed to 0. I have tried to write a script for above but the data is not entirely correct. Below is the script:

    CREATE TABLE #temp(
           [user_id] [varchar](100) NULL,
           [event_time] [varchar](100) NULL,
                 [value] [varchar](100) NULL
    )
    ;with CTE as(
    select a.user_id,a.event_time,a.value from events as a  where  a.user_id in
    (
    select distinct b.user_id from events b where   b.value=0) 
    )
    

    insert into #temp
    select * from CTE where user_id in(select distinct user_id from CTE where value<>0)

    ;WITH CTE1 AS (
    select a.*,ROW_NUMBER() OVER (PARTITION BY a.user_id ORDER BY a.event_time DESC) AS rn from #temp a inner join #temp b on
    a.user_id=b.user_id
    where a.event_time<b.event_time and a.value<>0 and b.value=0
    )
    SELECT * FROM CTE1 WHERE rn = 1;
    drop table #temp

    Can anyone check and help correct the query?



  • WITH cte AS (
        SELECT user_id, event_time, value,
               LAG(value) OVER (PARTITION BY user_id ORDER BY event_time) lag_value,
               LEAD(value) OVER (PARTITION BY user_id ORDER BY event_time) lead_value
        FROM source_table 
    )
    SELECT user_id, event_time, value
    FROM cte
    WHERE (value = 0 AND lag_value <> 0)
       OR (value <> 0 AND lead_value = 0)
    ORDER BY 1, 2
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2