SQL to get difference of rows (on Date column status change) in table

General
Enthusiast

SQL to get difference of rows (on Date column status change) in table

I have a table that contains telecom applications livetv data. Basically I'm looking for duration between the rows (lasttime column)  at status changes on one particular column.

timestp   box_num  chanel oprations universe lasttime Remotetime  Dat_tech 
20130312 12:21:32 1114303da3c5 1 3 0 20130312 12:21:22 20130312122127 12/03/2013 12:21:41.56
20130312 12:26:32 1114303da3c5 1 3 0 20130312 12:21:22 20130312122127 12/03/2013 12:26:41.56
20130312 12:31:32 1114303da3c5 1 3 0 20130312 12:21:22 20130312122127 12/03/2013 12:31:41.56
20130312 12:32:51 1114303da3c5 3 6 0 20130312 12:32:41 20130312123240 12/03/2013 12:33:04.87
20130312 12:37:51 1114303da3c5 3 6 0 20130312 12:32:41 20130312123240 12/03/2013 12:38:04.88
20130312 12:42:03 1114303da3c5 1 5 0 20130312 12:41:53 20130312124151 12/03/2013 12:42:13.23
20130312 12:47:03 1114303da3c5 1 5 0 20130312 12:41:53 20130312124228 12/03/2013 12:47:17.84
20130312 12:52:03 1114303da3c5 9 3 0 20130312 14:20:36 20130312142035 12/03/2013 14:21:04.79









In the above record set all the column values are same until the chanel coulmn values and lasttime column are not  changed .

Dat_tech is nthing but current_timestamp(0);

i want to calculate a duration column for new table which should contain  difference between the column lasttime when value chanel column  change (means 1 to 3 or 3 to 1 or 1 to 9 etc etc ).

can you please help me for this scenario ?

Thanks a lot in advance.

Tags (1)