Clever Geek Handbook

Flag dates occurring after the event within individuals

I have a dataset in long format (multiple lines per person, person = id) where the event (event = 1) should only appear once. When the event happened, there should be no more data from that person.

I want to create a request with a new variable called flag (flag = 1) if there is any write after the event.

For example, id 5 is labeled below because after the event a row of data appears for that person. All other scripts are fine.

So I need to group the ids together and then check for any date that occurs after the event date.

What's the most efficient way to achieve this in SQL?

EXAMPLE DATA (comma separated):

id,date,event,flag
1,01-Aug-14,0,0
1,02-Aug-14,0,0
2,01-Aug-14,0,0
2,02-Aug-14,1,0
3,01-Aug-14,1,0
4,01-Aug-14,0,0
5,01-Aug-14,0,0
5,02-Aug-14,1,0
5,03-Aug-14,0,1
+3


source to share


1 answer


I have used a subheading to count the number of events for id to date.

SQL Fiddle Demo



select id,
 case 
    when (select count(1) from yourTable t2  where t2.date < t1.date and t1.id = t2.id and t2.event = 1) > 0 then 1
    else 0 
  end as flag
from
yourTable t1
+2


source






More articles:


All Articles