SQLTeam.com | Weblogs | Forums

Difference between 2 dates on same column based on value in another column


#1

I have a table like below:

app_no    |       curr_time          updt_time         status
212461248 |	2016-04-30 20:26:54 |2016-05-04 08:36:17 |	Incomplete
212461248 |	2016-04-30 20:26:54 |2016-11-18 11:17:28 |	Error
212461248 |	2016-04-30 20:26:54 |2017-01-27 02:00:40 |	Refer
212461248 |	2016-04-30 20:26:54 |2017-08-12 23:59:02 |	Declined
212461251 |	2017-06-15 01:41:13 |2017-06-22 04:02:11 |	Error
212461251 |	2017-06-15 01:41:13 |2017-10-18 01:34:09 |	Declined
212461253 |	2016-06-21 00:56:23 |2016-06-24 11:44:17 |	Incomplete
212461253 |	2016-06-21 00:56:23	|2017-09-04 18:20:06 |	Error

I would like to get the time taken by each application to move from one status to another (from updt_time column)?

Ex: time take by 212461248 to move from incomplete to error, error to refer and refer to declined.

output should be like

status   time_taken
------  -----------
error     hh
refer     hh
declined  hh

Any ideas please?


#2

SELECT A.APP_NO

 ,A.STATUS

 ,DATEDIFF(HH, A.UPDT_TIME, B.UPDT_TIME) difference_hrs

FROM (

 SELECT APP_NO

      ,STATUS

      ,UPDT_TIME

      ,ROW_NUMBER() OVER (

          PARTITION BY APP_NO ORDER BY UPDT_TIME

          ) ROWNUMBER

 FROM APP_DATA

 ) A

INNER JOIN (

 SELECT APP_NO

      ,STATUS

      ,UPDT_TIME

      ,ROW_NUMBER() OVER (

          PARTITION BY APP_NO ORDER BY UPDT_TIME

          ) ROWNUMBER

 FROM APP_DATA

 ) B ON A.APP_NO = B.APP_NO

 AND A.ROWNUMBER = (B.ROWNUMBER - 1)

#3

Thanks Kazeem.