CODEWITHSUNDEEP
sqlsql-server
Michael
Michael

Reputation: 63

Removing records with the same name that were received within 24 hours of the last

I am using this code

DECLARE @ThisMonth DATETIME;
SET @ThisMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), 
'19000101');

select
id,
received,
siteid,
loggedbyteam,
closedbyteam,

from 
tbl.OpenAndClosed

where
received >= DATEADD(MONTH, -1, @ThisMonth)
and received <  @ThisMonth

On this table

Id  Received                  closed                    siteid          
1   2017-10-09 08:01:00.000   2017-10-09 16:00:00.000   500 
2   2017-10-09 08:01:00.000   2017-10-09 13:00:00.000   212 
3   2017-10-09 08:02:00.000   2017-10-09 08:07:30.540   499 
4   2017-10-09 08:10:00.000   2017-10-09 08:10:17.510   454 
5   2017-10-09 08:12:00.000   2017-10-09 08:15:12.297   454 
6   2017-10-09 08:35:00.000   2017-10-09 08:35:41.127   454 
7   2017-10-09 08:38:00.000   2017-10-09 14:50:00.000   696 
8   2017-10-09 08:38:00.000   2017-10-09 15:15:00.000   212 
9   2017-10-12 08:39:00.000   2017-10-12 15:41:37.527   454     
10  2017-10-09 08:49:00.000   2017-10-10 09:23:39.283   455

Which works fine

But what I want to be able to do is exclude the records that have the same siteid and were received within 24 hours of the last record being closed.

This is the result that I am looking for.

Id  Received                 closed                     siteid   
1   2017-10-09 08:01:00.000  2017-10-09 16:00:00.000    500 
2   2017-10-09 08:01:00.000  2017-10-09 13:00:00.000    212 
3   2017-10-09 08:02:00.000  2017-10-09 08:07:30.540    499 
5   2017-10-09 08:12:00.000  2017-10-09 08:15:12.297    454     
7   2017-10-09 08:38:00.000  2017-10-09 14:50:00.000    696 
8   2017-10-11 08:38:00.000  2017-10-11 15:15:00.000    212 
9   2017-10-12 08:39:00.000  2017-10-12 15:41:37.527    454 
10  2017-10-09 08:49:00.000  2017-10-10 09:23:39.283    455

ID4 and ID5 are removed from the results because it was within 24 hours of it being closed a new record was opened.

ID8 and ID9 remain because they not have a record being closed prior to 24 hours of it being opened.

Essentually, "exclude any record that has the same siteid and closed is with 24 hours of received."

If you need anymore info let me know.

Upvotes: 1

Views: 57

Answers (2)

Abhishek Pratap Singh
Abhishek Pratap Singh

Reputation: 11

First step is to identify which rows have duplicate values:

SELECT colname1, colname2, count(*)
FROM table
GROUP BY colname1, colname2
HAVING count(*) > 1

And then use this query for delete:

set rowcount 1
delete from table
where colname1=1 and colname2=1 and date = currentdate

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I think the following will do what you want:

select t.*
from (select id, received, closed, siteid, loggedbyteam, closedbyteam,
             lag(closed) over (partition by siteid order by closed) as prev_closed
      from tbl.OpenAndClosed oac
      where received >= DATEADD(MONTH, -1, @ThisMonth) and
            received <  @ThisMonth
     ) oac
where prev_closed < dateadd(day, -1, closed) or prev_closed is null;

Upvotes: 1

Related Questions