how to compare two range time for find duplicates records in sql

Question

how to compare two range time for find duplicates records in sql .

Example :-

time_start   time_end

1000         1400

1200         1400

above 1200 to 1400 repeat duplicates time

please help me.

can you give an example output, what difference are you looking at? i assume hours? — Matt, Nov 28 '14 at 08:47
I'm not sure "duplicate" is the right word here. I *think* you mean "overlapping" time ranges, which is a bit harder to do. — mabi, Nov 28 '14 at 08:58
what is the datatype of those columns? is it `varchar(4)` or `int`? — Deep, Nov 28 '14 at 09:09
Overlap with this question: http://stackoverflow.com/questions/6571538/checking-a-table-for-time-overlap — mabi, Nov 28 '14 at 11:17

score 0 · Answer 1 · answered Nov 28 '14 at 08:49

0

This will get you the difference between the two dates

SELECT DATEDIFF(hh,time_start,time_end)

This will group the duplicates and give you a count

SELECT 
    DATEDIFF(hh,time_start,time_end) AS Diff,
    COUNT(*) AS DuplicateCount
FROM 
    table
DATEDIFF(hh,time_start,time_end)

answered Nov 28 '14 at 08:49

Matt

You might want to mention that this is mysql specific. – mabi Nov 28 '14 at 08:56
It will show how many of the record has same difference in `time_start` and `time_end`. – Deep Nov 28 '14 at 09:05

score 0 · Answer 2 · answered Nov 28 '14 at 09:04

0

you have to fixed the start time as static and end time common.

use this code:

select * from table_name where time_start >= 1200 and time_end <= 1400;

answered Nov 28 '14 at 09:04

sreesiva

2 Answers2