In the dataset below, I'd like to group the rows by cust,num and 30 second intervals, then mark the min times with a 1 and leave the max as 0.
create table dctable(cust varchar, num int, tim timestamp, mins smallint);
insert into dctable values ('Jim', 5, '8/27/2015 1:10:00', 0);
insert into dctable values ('Jim', 5, '8/27/2015 1:10:15', 0);
insert into dctable values ('Jim', 5, '8/27/2015 1:10:28', 0);
insert into dctable values ('Jane',8, '7/3/2018 2:20:50', 0);
insert into dctable values ('Jane',8, '7/3/2018 2:21:05', 0);
insert into dctable values ('Jane',8, '7/3/2018 2:21:10', 0);
insert into dctable values ('Jane',8, '7/3/2018 2:30:55', 0);
insert into dctable values ('Jack',4, '1/1/2018 10:20:25', 0);
insert into dctable values ('Jake',9, '1/1/2018 10:20:05', 0);
insert into dctable values ('Jake',2, '9/15/2015 2:20:55', 0);
The result I would like to get is this:
cust num tim mins
Jim 5 8/27/2015 1:10:00 1
Jim 5 8/27/2015 1:10:15 1
Jim 5 8/27/2015 1:10:28 0
Jane 8 7/3/2018 2:20:50 1
Jane 8 7/3/2018 2:21:05 1
Jane 8 7/3/2018 2:21:10 0
Jane 8 7/3/2018 2:30:55 0
Jake 2 1/1/2018 10:20:25 0
Jake 9 1/1/2018 10:20:05 0
Jack 4 9/15/2015 2:20:55 0
Tried this but not working:
update dctable
set mins = 1
from
(
select cust, num, max(tim) as maxtime
from dctable
group by cust, num, (DATEDIFF(SECOND, 0, tim) / 30)
) b
where dctable.cust = b.cust
and dctable.num = b.num
and dctable.tim <= b.maxtime
select * from dctable;
Thanks for any help.