CODEWITHSUNDEEP
sqlsql-server
dudo
dudo

Reputation: 17

select previous row date to current row date

i have table like this,

REPORT_ID         USER_ID     ACTION_TYPE ACTION_START            
----------------- ----------- ----------- ----------------------- 
20122511549-621   621         OPEN        2012-02-05 11:05:49.000 
20122511549-621   621         CLOSE       2012-02-19 10:53:28.000 
20132121098-621   621         OPEN        2013-02-12 10:09:08.807 
20132121098-621   621         ACCEPT      2013-02-12 11:16:17.167 
20132121098-621   621         COMMENT     2013-02-12 12:20:17.167 
20132121098-621   621         CLOSE       2013-03-22 16:20:17.167 
201321814390-621  621         OPEN        2013-02-18 14:39:00.157 
201322010148-707  707         OPEN        2013-02-20 10:01:48.693 
201322010148-707  707         RETRIEVE    2013-10-29 13:22:05.017

what i am trying without much success is to select ACTION_START from previous row to current row as prev_action_start

under condition:

when REPORT_ID = REPORT_ID for all rows except when ACTION_TYPE='OPEN' then current row as prev_action_start should be ACTION_START to look like this:

REPORT_ID         USER_ID     ACTION_TYPE ACTION_START            prev_action_start 
----------------- ----------- ----------- ----------------------- ------------------------
20122511549-621   621         OPEN        2012-02-05 11:05:49.000 2012-02-05 11:05:49.000
20122511549-621   621         CLOSE       2012-02-19 10:53:28.000 2012-02-05 11:05:49.000
20132121098-621   621         OPEN        2013-02-12 10:09:08.807 2013-02-12 10:09:08.807
20132121098-621   621         ACCEPT      2013-02-12 11:16:17.167 2013-02-12 10:09:08.807
20132121098-621   621         COMMENT     2013-02-12 12:20:17.167 2013-02-12 11:16:17.167
20132121098-621   621         CLOSE       2013-03-22 16:20:17.167 2013-02-12 12:20:17.167
201321814390-621  621         OPEN        2013-02-18 14:39:00.157 2013-02-18 14:39:00.157
201322010148-707  707         OPEN        2013-02-20 10:01:48.693 2013-02-20 10:01:48.693
201322010148-707  707         RETRIEVE    2013-10-29 13:22:05.017 2013-02-20 10:01:48.693

if any1 can help i would appreciate?

Upvotes: 0

Views: 184

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

In SQL Server 2012, you can use the lag() function. In earlier versions, you need to do an explicit join or correlated subquery. I find the latter the easiest to follow:

select t.*,
       (case when Action_Type = 'Open' then Action_Start else prev1
        end) as prev_action_start
from (select t.*,
             (select top 1 Action_Start
              from table t2
              where t2.ReportId = t.ReportId and
                    t2.Action_Start < t.Action_Start
              order by Action_Start desc
             ) as prev1
      from table t
     ) t;

You can actually put the subquery in the case statement (eliminating the subquery), but I think it is clearer to split the logic in two.

Upvotes: 3

Related Questions