I have records with columns: ID, Time_End and Attribute.
I need to delete all records,
WHERE Time_End = '1990-01-01 00:00:00.000' AND Attribute <> '9'
but only:
- if the next row does not have the same attribute number
or
- the next row has the same attribute number and a
Time_End value of 1990-01-01 00:00:00.000
For example:
ID Time_End Attribute
---------------------------------------------
235 1990-01-01 00:00:00.000 5 /delete
236 1990-01-01 00:00:00.000 5 /delete
237 1990-01-01 00:00:00.000 5
238 2016-10-10 23:45:40.000 5
ID Time_End Attribute
---------------------------------------------
312 1990-01-01 00:00:00.000 8 /delete
313 2016-01-09 18:00:00.000 6
314 1990-01-01 00:00:00.000 4 /delete
315 1990-01-01 00:00:00.000 7
316 2016-10-10 23:45:40.000 7
Our customer have 50 database tables with thousands of records in every table (and of course more columns, I mentioned only those, which have impact on solution). Records are send in to the database from PLC, but sometimes (we don't know why) PLC send also wrong records.
So what I need is a query which finds those wrong records and deletes them. :)
Anybody who knows how the SQL code should look like?
Please see my SQL below. First, we collect ids to delete using two window functions (LEAD) to get the next row needed data. Then, with all needed data computed, apply the evaluation rules proposed by the OP. Last, use the obtained ids to delete the affected records of the tablet by id with an in clause.
DELETE toDeleteTable
WHERE toDeleteTable.id IN (WITH dataSet
AS (SELECT toDeleteTable.id,
toDeleteTable.time_end,
toDeleteTable.attribute,
LEAD(toDeleteTable.time_end,1,0) OVER (ORDER BY toDeleteTable.id) AS next_time_end,
LEAD(toDeleteTable.attribute,1,0) OVER (ORDER BY toDeleteTable.id) AS next_attribute
FROM toDeleteTable)
SELECT dataSet.id
FROM dataSet
WHERE dataSet.time_end = '1990-01-01 00:00:00.000'
AND dataSet.attribute <> '9'
AND ( (dataSet.next_attribute = dataSet.attribute AND dataSet.next_time_end = '1990-01-01 00:00:00.000')
OR dataSet.next_attribute <> dataSet.attribute)
)
You can accomplish this with a simple apply join. The below should give you enough to make this work for your needs without doing anything complex:
declare @t table(ID int
,Time_End datetime
,Attribute int
);
insert into @t values(235,'1990-01-01 00:00:00.000',5),(236,'1990-01-01 00:00:00.000',5),(237,'1990-01-01 00:00:00.000',5),(238,'2016-10-10 23:45:40.000',5),(312,'1990-01-01 00:00:00.000',8),(313,'2016-01-09 18:00:00.000',6),(314,'1990-01-01 00:00:00.000',4),(315,'1990-01-01 00:00:00.000',7),(316,'2016-10-10 23:45:40.000',7);
select t.*
,tm.*
from @t t
outer apply (select top 1 tt.Time_End
,tt.Attribute
from @t tt
where t.ID < tt.ID
order by tt.ID
) tm
where t.Attribute <> tm.Attribute
or (t.Attribute = tm.Attribute
and tm.Time_End = '1990-01-01 00:00:00.000'
);
I think you can use ROW_NUMBER() like this:
;WITH t AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Time_End ORDER BY ID DESC) AS seq
FROM yourTable
WHERE Attribute <> '9'
AND Time_End = CAST('1990-01-01 00:00:00.000' as datetime)
)
DELETE FROM t
WHERE seq > 1;
Not Tested - HTH ;).
