Mansi Chaudhari Mansi Chaudhari - 19 days ago 5
SQL Question

Want to add new column which is calculated from existing column

I have a one table below which has call data inserted. Now I want to calculate picktime and close time of call

Create table calldata (calldate Datetime,picktime datetime,status varchar(50))

insert into calldata values('2016-11-18 04:43:37 PM','2016-11-18 04:43:57 PM','ATTENDED')
insert into calldata values('2016-11-18 04:43:37 PM','2016-11-18 04:44:25 PM','ESCALATED')
insert into calldata values('2016-11-18 04:43:37 PM','2016-11-18 04:49:52 PM','ESCALATED')
insert into calldata values('2016-11-18 04:43:37 PM','2016-11-18 04:50:20 PM','CLOSED')

select * from calldata




calldate | picktime | status
--------------------------------------------------------------
2016-11-18 16:43:37.000 | 2016-11-18 16:43:57.000 | ATTENDED
2016-11-18 16:43:37.000 | 2016-11-18 16:44:25.000 | ESCALATED
2016-11-18 16:43:37.000 | 2016-11-18 16:49:52.000 | ESCALATED
2016-11-18 16:43:37.000 | 2016-11-18 16:50:20.000 | CLOSED





Now I want data like this from above table:

calldate | picktime | CloseTime | status
-------------------------------------------------------------------------------------------
2016-11-18 16:43:37.000 | 2016-11-18 16:43:57.000 | 2016-11-18 16:44:25.000 | ATTENDED
2016-11-18 16:43:37.000 | 2016-11-18 16:44:25.000 | 2016-11-18 16:49:52.00 | ESCALATED
2016-11-18 16:43:37.000 | 2016-11-18 16:49:52.000 | 2016-11-18 16:50:20.00 | CLOSED
2016-11-18 16:43:37.000 | 2016-11-18 16:50:20.000 | | CLOSED

Answer

something like this?

LEAD(picktime, 1, null) OVER (PARTITION BY calldate ORDER BY picktime)?

It gets the next pictime for the same calldate.