SQL deletes all consecutive records that have the same value, leaving only the first and last

Question

I have a situation where I need to delete consecutive records that use the same field value ("Failed to notify client"), however I need to leave the first and last instance

sample data

date             type    log
20/11/2014 09:05 System, Order Added
20/11/2014 09:18 Mark,   Invoice Printed
20/11/2014 10:00 System, Failed to notify Customer
20/11/2014 10:05 System, Failed to notify Customer
20/11/2014 10:10 System, Failed to notify Customer
20/11/2014 10:15 System, Failed to notify Customer
20/11/2014 10:20 System, Failed to notify Customer
20/11/2014 12:05 System, Order Completed

final conclusion

date             type    log
20/11/2014 09:05 System, Order Added
20/11/2014 09:18 Mark,   Invoice Printed
20/11/2014 10:00 System, Failed to notify Customer
20/11/2014 10:20 System, Failed to notify Customer
20/11/2014 12:05 System, Order Completed

Is there a way to formulate a sql server query to achieve this? for life me i cant look back as i approach this

Answer 1

Try the following:

Sample data:

use tempdb

create table temp(
    [date]  datetime,
    type    varchar(100),
    [log]   varchar(100)
)
insert into temp values
('11/20/2014 09:05', 'System', 'Order Added'),
('11/20/2014 09:18', 'Mark', 'Invoice Printed'),
('11/20/2014 10:00', 'System', 'Failed to notify Customer'),
('11/20/2014 10:05', 'System', 'Failed to notify Customer'),
('11/20/2014 10:10', 'System', 'Failed to notify Customer'),
('11/20/2014 10:15', 'System', 'Failed to notify Customer'),
('11/20/2014 10:20', 'System', 'Failed to notify Customer'),
('11/20/2014 12:05', 'System', 'Order Completed');

Solution using ROW_NUMBER():

with cte as(
    select
        *,
        rn = row_number() over(partition by log order by [date]),
        cc = count(*) over(partition by log)
    from temp
    where
        log = 'Failed to notify Customer'
)
delete
from cte
where
    rn > 1 and rn < cc

select * from temp
drop table temp

Answer 2

select * from table-name where  log='Failed to notify Customer' 
group by log having date>min(date) and date<max(date);

This will allow you to select the desired lines and delete these lines.

P.S - , .

Answer 3

select the table that contains the maximum (date) for each client and the union that contains the one that contains the min (date) for each client. Then remove from the table where not in your union. Something like this (untested)

DELETE FROM Table
WHERE NOT EXISTS
(SELECT Max(Date), Type, Log
FROM Table
WHERE type = 'System' AND log = 'Failed to notify customer'
GROUP BY Type, Log
UNION ALL
SELECT Min(Date), Type, Log
FROM Table
WHERE type = 'System' AND log = 'Failed to notify customer'
GROUP BY Type, Log)

Answer 4

We can use the Group By and Have clause to get a log value that has an account greater than 1, and then we can get the MIN and MAX dates for the same.

Check out this script:

--Create table
Create table #test
(
date1 datetime,
type varchar(25),
log1 varchar(100)
)

--Inserting data
Set dateformat dmy
Insert into #Test values(cast('20/11/2014 09:05' as datetime), 'System', 'Order Added')
Insert into #Test values(cast('20/11/2014 09:18' as datetime), 'Mark',   'Invoice Printed')
Insert into #Test values(cast('20/11/2014 10:00' as datetime), 'System', 'Failed to notify Customer')
Insert into #Test values(cast('20/11/2014 10:05' as datetime), 'System', 'Failed to notify Customer')
Insert into #Test values(cast('20/11/2014 10:10' as datetime), 'System', 'Failed to notify Customer')
Insert into #Test values(cast('20/11/2014 10:15' as datetime), 'System', 'Failed to notify Customer')
Insert into #Test values(cast('20/11/2014 10:20' as datetime), 'System', 'Failed to notify Customer')
Insert into #Test values(cast('20/11/2014 12:05' as datetime), 'System', 'Order Completed')

--Delete operation
Delete From #test 
Where date1 <> (Select MIN(date1) from #Test where log1 = ((SELECT Log1 from #test group by log1 having COUNT(log1) > 1)))
and date1 <> (Select MAX(date1) from #Test where log1 = ((SELECT Log1 from #test group by log1 having COUNT(log1) > 1)))
and log1 = (SELECT Log1 from #test group by log1 having COUNT(log1) > 1)

--Checking output
Select * from #Test