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?

New contributor
Rathore is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
3
  • 1
    This is a well-known problem, known as gaps and islands.
    – vonPryz
    Jan 12 at 7:54
  • @vonPryz No, it is similar only...
    – Akina
    Jan 12 at 8:04
  • 3
    [event_time] [varchar](100) NULL, Start over - this is fundamentally flawed. Use the appropriate datatype. Add some rows for November 2021 and you will see why. That is the most important of multiple bad habits you have developed.
    – SMor
    Jan 12 at 12:59
1
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
1
  • works like a charm!! thanks a lot.
    – Rathore
    Jan 13 at 5:44

Your Answer

Rathore is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.