Time Difference Calculation (amended v2), Sql Server

Time Difference Calculation (amended v2)

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

create table actions (productid varchar(10), record_created datetime, action varchar(100));

insert into actions values ('A', '2018-01-11T17:24:00', 'action 1');
insert into actions values ('A', '2018-01-11T17:32:00', 'action 2');
insert into actions values ('A', '2018-01-17T16:41:00', 'action 3');
insert into actions values ('A', '2018-01-17T16:41:00', 'action 2');
insert into actions values ('A', '2018-01-17T16:44:00', 'action 3');
insert into actions values ('A', '2018-01-18T11:12:00', 'action 4');
insert into actions values ('A', '2018-01-18T11:12:00', 'action 3');
insert into actions values ('A', '2018-01-18T11:13:00', 'action 4');
insert into actions values ('A', '2018-01-25T14:44:00', 'action 5');
insert into actions values ('B', '2018-01-11T17:24:00', 'action 1');
insert into actions values ('B', '2018-01-11T17:32:00', 'action 2');
insert into actions values ('B', '2018-01-17T16:41:00', 'action 3');
insert into actions values ('B', '2018-01-17T16:41:00', 'action 2');
insert into actions values ('B', '2018-01-17T16:44:00', 'action 3');
insert into actions values ('B', '2018-01-18T11:12:00', 'action 4');
insert into actions values ('B', '2018-01-18T11:12:00', 'action 3');
insert into actions values ('B', '2018-01-18T11:13:00', 'action 4');
insert into actions values ('B', '2018-01-25T14:44:00', 'action 5');
insert into actions values ('C', '2018-01-11T17:24:00', 'action 1');
insert into actions values ('C', '2018-01-11T17:32:00', 'action 2');
insert into actions values ('C', '2018-01-17T16:41:00', 'action 3');
insert into actions values ('C', '2018-01-17T16:41:00', 'action 2');
insert into actions values ('C', '2018-01-17T16:44:00', 'action 3');
insert into actions values ('C', '2018-01-18T11:12:00', 'action 4');
insert into actions values ('C', '2018-01-18T11:12:00', 'action 3');
insert into actions values ('C', '2018-01-18T11:13:00', 'action 4');
insert into actions values ('C', '2018-01-26T14:44:00', 'action 5');

select 
  productid,
  action, 
  max(record_created),
  datediff(day,
    lag(max(record_created)) over (order by productid, max(record_created)), max(record_created)) as diff
from actions
group by productid, action
order by productid, action;

drop table actions;


run \| edit \| history \| help	0