Convert table of user state changes into Login segments table

quinestor 0
Viewed 32

I have a table with user statuses changes like this:

User New state Start Time End Time
joe st0 08:00:00 08:01:00
joe st1 08:01:00 09:00:00
alice st1 08:30:00 09:01:00
joe stX 09:00:00 11:00:00
alice st2 09:01:00 11:50:00
joe st3 11:20:00 14:50:00
joe stx 14:50:00 18:50:00

Where stx is an offline state. Id like to derive a table which will show me the segments of login-logout for each user. After transforming the above, the result should be:

User Start Time End Time
joe 08:00:00 11:00:00
alice 08:30:00 now()
joe 11:20:00 18:50:00

Any guide about how I could achieve that?

Using PostgreSQL 8.0.2

sql postgresql
1 Answers

For sql server you could do it by getting for each row

  • the lowest starttime but not before the prior stX
  • the highest endtime until the next stX

and then group by on that

Here is a complete example

The query that works with your sample data looks like this

select s.username,
       isnull(tstart.StartTime, s.StartTime) as StartTime,
       tend.EndTime
from   status s
  outer apply ( select top 1
                       s2.StartTime
                from   status s2
                  outer apply ( select top 1 s3.StartTime 
                                from   status s3 
                                where  s3.username = s.username 
                                and    s3.StartTime < s.StartTime 
                                and    s3.NewState = 'stX'
                                order by s3.StartTime desc
                              ) s4
                where  s2.username = s.username
                and    s2.StartTime < s.StartTime 
                and    (s4.StartTime is null or s2.StartTime > s4.StartTime)
                order by s2.StartTime
              ) tstart 
  outer apply ( select top 1
                       s2.EndTime
                from   status s2
                where  s2.username = s.username
                and    s2.EndTime >= s.EndTime
                and    s2.NewState = 'stX'
                order by s2.EndTime
              ) tend
group by  s.username,
          isnull(tstart.StartTime, s.StartTime),
          tend.EndTime
order by isnull(tstart.StartTime, s.StartTime)

And it returns this as result

username StartTime EndTime
joe 08:00:00.0000000 11:00:00.0000000
alice 08:30:00.0000000 null
joe 11:20:00.0000000 18:50:00.0000000
GuidoG0
Related