CODEWITHSUNDEEP
sql-server
Fauzi Rachman
Fauzi Rachman

Reputation: 101

Delete Record with Specific Time in SQL Server

This is my table records

field_date  field_time  field_id 
20161202    12:56:00    309073 
20161202    12:57:00    208901 
20161202    12:58:00    208901 
20161202    13:15:00    208901

with the same id there (208901) I want to delete the ID with time interval below 15 minutes, for example there, this is the result I want

field_date  field_time  field_id 
20161202    12:56:00    309073 
20161202    12:57:00    208901 
20161202    13:15:00    208901

id with 12:58:00 time was deleted because it has only 1 minute interval with the id first record, but the 13:15:00 not deleted because the interval is more than 15 minutes from the first record.

Here is my code, it still show the 12:58:00 records and I want to delete it so I'm going to only show the record with less than 15 minutes interval, this code just show all records (not the duplicate record that i want), what's wrong there?

SELECT *
FROM tbl_name WHERE EXISTS (SELECT 1 FROM tbl_name t2 
        WHERE(t2.field_id = tbl_name.field_id AND 
        DATEDIFF(MINUTE, t2.field_time, tbl_name.field_time) <= 15) 
        )
        ORDER BY field_time

Upvotes: 2

Views: 77

Answers (4)

KumarHarsh
KumarHarsh

Reputation: 5094

Try this,

DECLARE @DataSource TABLE
(
    [field_date] INT
   ,[field_time] VARCHAR(8)
   ,[field_id] INT
);

INSERT INTO @DataSource ([field_date], [field_time], [field_id])
VALUES (20161202, '12:56:00', 309073 )
      ,(20161202, '12:57:00', 208901)
      ,(20161202, '12:58:00', 208901)
      ,(20161202, '13:15:00', 208901);
;with CTE as
(
select *,ROW_NUMBER()over(PARTITION by [field_date], [field_id] ORDER BY [field_time] ASC)rn  from @DataSource A
)

, CTE1 as
(
select *,1 flg from cte where rn=1
UNION ALL
select c.*,case when (DATEDIFF(MINUTE,  c1.field_time,c.field_time) <= 15) then 0 else 1 end  from cte c
inner join cte1 c1 on c1.[field_id]=c.[field_id]
where c.rn=c1.rn+1
)
select * from cte1
where flg=1
order by [field_id],field_time

Upvotes: 0

gotqn
gotqn

Reputation: 43646

What about this (for SQL Server 2012+):

DECLARE @DataSource TABLE
(
    [field_date] INT
   ,[field_time] VARCHAR(8)
   ,[field_id] INT
);

INSERT INTO @DataSource ([field_date], [field_time], [field_id])
VALUES (20161202, '12:56:00', 309073 )
      ,(20161202, '12:57:00', 208901)
      ,(20161202, '12:58:00', 208901)
      ,(20161202, '13:15:00', 208901);

WITH DataSource ([field_date], [field_time], [field_id], [timeDiff]) AS
(
    SELECT *
         ,DATEDIFF(MINUTE, MIN([field_time]) OVER(PARTITION BY [field_date], [field_id] ORDER BY [field_time] ASC), [field_time])
    FROM @DataSource
)
SELECT *
FROM DataSource
WHERE [timeDiff] = 0
    OR [timeDiff] > 15
ORDER BY field_time;

enter image description here

Upvotes: 1

Ranjana Ghimire
Ranjana Ghimire

Reputation: 1815

Try as follows:

DECLARE @DATEDIFF TABLE (ID INT IDENTITY(1,1),FIELD_DATE DATE,FIELD_TIME TIME,FIELD_ID INT)
INSERT INTO @DATEDIFF SELECT * FROM TABLE1
DECLARE @FIELD_ID INT,@FIELD_DATE DATE,@FIELD_TIME TIME,@ID INT,@NEXT_TIME TIME,@NEXT_DATE DATE

DECLARE C CURSOR FOR 
SELECT DISTINCT FIELD_ID FROM @DATEDIFF
OPEN C
FETCH NEXT FROM C INTO @FIELD_ID
WHILE @@FETCH_STATUS=0
BEGIN

    DECLARE D CURSOR FOR
    SELECT ID,FIELD_DATE ,FIELD_TIME FROM @DATEDIFF WHERE FIELD_ID = @FIELD_ID
    OPEN D
    FETCH NEXT FROM D INTO @ID,@FIELD_DATE,@FIELD_TIME
    WHILE @@FETCH_STATUS=0
    BEGIN
    SET @NEXT_TIME=(SELECT FIELD_TIME FROM @DATEDIFF WHERE ID=@ID+1 AND FIELD_ID = @FIELD_ID )
    SET @NEXT_DATE=(SELECT FIELD_DATE FROM @DATEDIFF WHERE ID=@ID+1 AND FIELD_ID = @FIELD_ID )

    IF EXISTS(SELECT FIELD_TIME FROM @DATEDIFF WHERE ID=@ID+1 AND FIELD_ID = @FIELD_ID)
    BEGIN
    IF ( DATEDIFF (MI,CAST(@FIELD_DATE AS VARCHAR(100))+' '+CAST(@FIELD_TIME AS VARCHAR(100)),CAST(@NEXT_DATE AS VARCHAR(100))+' '+CAST(@NEXT_TIME AS VARCHAR(100))))<15
    DELETE FROM @DATEDIFF  WHERE ID=@ID+1 AND FIELD_ID = @FIELD_ID
    END
    FETCH NEXT FROM D INTO @ID,@FIELD_DATE,@FIELD_TIME
    END
    CLOSE D
    DEALLOCATE D

FETCH NEXT FROM C INTO @FIELD_ID
END
CLOSE C
DEALLOCATE C

SELECT * FROM @DATEDIFF

Upvotes: 0

Eric
Eric

Reputation: 5743

DECLARE @Data TABLE ([field_date] int, [field_time] varchar(8), [field_id] int);

INSERT INTO @Data
VALUES
    (20161202, '12:56:00', 309073),
    (20161202, '12:57:00', 208901),
    (20161202, '12:58:00', 208901),
    (20161202, '13:15:00', 208901)
;


;WITH data AS 
(
    SELECT 
        *, 
        -- A field easier to use for comparison, e.g. cross date
        CONVERT(datetime, CAST(field_date AS char(8)), 112) + CONVERT(time, field_time) AS combined_time 
    FROM @Data
)
DELETE curr 
FROM 
    data curr 
    OUTER APPLY 
    (
        SELECT TOP 1 * 
        FROM data prev 
        WHERE 
            prev.field_id = curr.field_id 
            -- assume the record does not contain duplicate record of field_time
            AND prev.combined_time < curr.combined_time 
        ORDER BY combined_time DESC
    ) AS prev
WHERE DATEDIFF(MINUTE, prev.combined_time, curr.combined_time) <= 15

-- Result
SELECT * FROM @Data

Upvotes: 0

Related Questions