Mixing
Need SQL Query to identify duplicate based on time interval
I Have table structure like below,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 01:00:00| 2011-12-03 04:00:00| 101
2 | 2011-12-22 09:00:00| 2011-12-22 09:30:00| 102
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
3 | 2012-01-02 07:00:00| 2012-01-02 07:00:00| 104
3 | 2012-01-25 16:30:00| 2012-01-25 13:50:36| 105
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
As per my requirement, a person took one event within a particular time slot. In the above example, personid '1' should take only the event '101'. because event '101' timing is 01 AM to 04 AM on 03-Dec. The other event on the same day is '103 & 106'. both event timings are fall in between the event timing of '101'.
I need a query to identify the duplicate of the start time and end time fall under the existing start time and end time. Which is how can I identify the duplicate event entries 103 & 106 of event 101 using SQL query.
My expected result would be,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
mysql duplicates
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
add a comment |
I Have table structure like below,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 01:00:00| 2011-12-03 04:00:00| 101
2 | 2011-12-22 09:00:00| 2011-12-22 09:30:00| 102
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
3 | 2012-01-02 07:00:00| 2012-01-02 07:00:00| 104
3 | 2012-01-25 16:30:00| 2012-01-25 13:50:36| 105
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
As per my requirement, a person took one event within a particular time slot. In the above example, personid '1' should take only the event '101'. because event '101' timing is 01 AM to 04 AM on 03-Dec. The other event on the same day is '103 & 106'. both event timings are fall in between the event timing of '101'.
I need a query to identify the duplicate of the start time and end time fall under the existing start time and end time. Which is how can I identify the duplicate event entries 103 & 106 of event 101 using SQL query.
My expected result would be,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
mysql duplicates
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
So you want to find rows where the same person has an event that conflicts with another event they have? Do the times just have to overlap, or do you only want to see ones where the time range is a subset of another event's times?
– ysth
Jan 2 at 10:17
@ysth I want to see ones where the time range is a subset of another event's times for the same user.
– Ravichandran Jothi
Jan 2 at 11:12
add a comment |
I Have table structure like below,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 01:00:00| 2011-12-03 04:00:00| 101
2 | 2011-12-22 09:00:00| 2011-12-22 09:30:00| 102
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
3 | 2012-01-02 07:00:00| 2012-01-02 07:00:00| 104
3 | 2012-01-25 16:30:00| 2012-01-25 13:50:36| 105
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
As per my requirement, a person took one event within a particular time slot. In the above example, personid '1' should take only the event '101'. because event '101' timing is 01 AM to 04 AM on 03-Dec. The other event on the same day is '103 & 106'. both event timings are fall in between the event timing of '101'.
I need a query to identify the duplicate of the start time and end time fall under the existing start time and end time. Which is how can I identify the duplicate event entries 103 & 106 of event 101 using SQL query.
My expected result would be,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
mysql duplicates
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
I Have table structure like below,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 01:00:00| 2011-12-03 04:00:00| 101
2 | 2011-12-22 09:00:00| 2011-12-22 09:30:00| 102
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
3 | 2012-01-02 07:00:00| 2012-01-02 07:00:00| 104
3 | 2012-01-25 16:30:00| 2012-01-25 13:50:36| 105
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
As per my requirement, a person took one event within a particular time slot. In the above example, personid '1' should take only the event '101'. because event '101' timing is 01 AM to 04 AM on 03-Dec. The other event on the same day is '103 & 106'. both event timings are fall in between the event timing of '101'.
I need a query to identify the duplicate of the start time and end time fall under the existing start time and end time. Which is how can I identify the duplicate event entries 103 & 106 of event 101 using SQL query.
My expected result would be,
personID | Starttime | Endtime EventID
---------| ------------------- | --------------------|---------
1 | 2011-12-03 02:00:00| 2011-12-03 02:30:00| 103
1 | 2011-12-03 03:00:00| 2011-12-03 04:00:00| 106
mysql duplicates
mysql duplicates
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
asked Jan 2 at 7:48
Ravichandran JothiRavichandran Jothi
1,61093979
1,61093979
So you want to find rows where the same person has an event that conflicts with another event they have? Do the times just have to overlap, or do you only want to see ones where the time range is a subset of another event's times?
– ysth
Jan 2 at 10:17
@ysth I want to see ones where the time range is a subset of another event's times for the same user.
– Ravichandran Jothi
Jan 2 at 11:12
add a comment |
So you want to find rows where the same person has an event that conflicts with another event they have? Do the times just have to overlap, or do you only want to see ones where the time range is a subset of another event's times?
– ysth
Jan 2 at 10:17
@ysth I want to see ones where the time range is a subset of another event's times for the same user.
– Ravichandran Jothi
Jan 2 at 11:12
So you want to find rows where the same person has an event that conflicts with another event they have? Do the times just have to overlap, or do you only want to see ones where the time range is a subset of another event's times?
– ysth
Jan 2 at 10:17
So you want to find rows where the same person has an event that conflicts with another event they have? Do the times just have to overlap, or do you only want to see ones where the time range is a subset of another event's times?
– ysth
Jan 2 at 10:17
@ysth I want to see ones where the time range is a subset of another event's times for the same user.
– Ravichandran Jothi
Jan 2 at 11:12
@ysth I want to see ones where the time range is a subset of another event's times for the same user.
– Ravichandran Jothi
Jan 2 at 11:12
add a comment |
1 Answer
1
active
oldest
votes
You can do this by joining your table against itself. The query below will return all the events which sit within another event and are assigned to the same person as the containing event; if you want to return details of the containing event instead you can use SELECT yourtablename.* instead of SELECT subset.*.
select subset.*
from yourtablename
join yourtablename subset
on subset.personID=yourtablename.personID
and subset.EventID <> yourtablename.EventID
and subset.Starttime >= yourtablename.Starttime
and subset.Endtime <= yourtablename.Endtime
edited Jan 2 at 18:39
Ade Stringer
2,3441223
answered Jan 2 at 14:59
ysthysth
78.5k495192
@AdeStringer thanks
– ysth
Jan 2 at 20:21
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002906%2fneed-sql-query-to-identify-duplicate-based-on-time-interval%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can do this by joining your table against itself. The query below will return all the events which sit within another event and are assigned to the same person as the containing event; if you want to return details of the containing event instead you can use SELECT yourtablename.* instead of SELECT subset.*.
select subset.*
from yourtablename
join yourtablename subset
on subset.personID=yourtablename.personID
and subset.EventID <> yourtablename.EventID
and subset.Starttime >= yourtablename.Starttime
and subset.Endtime <= yourtablename.Endtime
edited Jan 2 at 18:39
Ade Stringer
2,3441223
answered Jan 2 at 14:59
ysthysth
78.5k495192
@AdeStringer thanks
– ysth
Jan 2 at 20:21
add a comment |
You can do this by joining your table against itself. The query below will return all the events which sit within another event and are assigned to the same person as the containing event; if you want to return details of the containing event instead you can use SELECT yourtablename.* instead of SELECT subset.*.
select subset.*
from yourtablename
join yourtablename subset
on subset.personID=yourtablename.personID
and subset.EventID <> yourtablename.EventID
and subset.Starttime >= yourtablename.Starttime
and subset.Endtime <= yourtablename.Endtime
edited Jan 2 at 18:39
Ade Stringer
2,3441223
answered Jan 2 at 14:59
ysthysth
78.5k495192
@AdeStringer thanks
– ysth
Jan 2 at 20:21
add a comment |
You can do this by joining your table against itself. The query below will return all the events which sit within another event and are assigned to the same person as the containing event; if you want to return details of the containing event instead you can use SELECT yourtablename.* instead of SELECT subset.*.
select subset.*
from yourtablename
join yourtablename subset
on subset.personID=yourtablename.personID
and subset.EventID <> yourtablename.EventID
and subset.Starttime >= yourtablename.Starttime
and subset.Endtime <= yourtablename.Endtime
edited Jan 2 at 18:39
Ade Stringer
2,3441223
answered Jan 2 at 14:59
ysthysth
78.5k495192
You can do this by joining your table against itself. The query below will return all the events which sit within another event and are assigned to the same person as the containing event; if you want to return details of the containing event instead you can use SELECT yourtablename.* instead of SELECT subset.*.
select subset.*
from yourtablename
join yourtablename subset
on subset.personID=yourtablename.personID
and subset.EventID <> yourtablename.EventID
and subset.Starttime >= yourtablename.Starttime
and subset.Endtime <= yourtablename.Endtime
edited Jan 2 at 18:39
Ade Stringer
2,3441223
answered Jan 2 at 14:59
ysthysth
78.5k495192
edited Jan 2 at 18:39
Ade Stringer
2,3441223
edited Jan 2 at 18:39
Ade Stringer
2,3441223
edited Jan 2 at 18:39
Ade Stringer
2,3441223
2,3441223
answered Jan 2 at 14:59
ysthysth
78.5k495192
answered Jan 2 at 14:59
ysthysth
78.5k495192
answered Jan 2 at 14:59
ysthysth
78.5k495192
78.5k495192
@AdeStringer thanks
– ysth
Jan 2 at 20:21
add a comment |
@AdeStringer thanks
– ysth
Jan 2 at 20:21
@AdeStringer thanks
– ysth
Jan 2 at 20:21
@AdeStringer thanks
– ysth
Jan 2 at 20:21
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54002906%2fneed-sql-query-to-identify-duplicate-based-on-time-interval%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
So you want to find rows where the same person has an event that conflicts with another event they have? Do the times just have to overlap, or do you only want to see ones where the time range is a subset of another event's times?
– ysth
Jan 2 at 10:17
@ysth I want to see ones where the time range is a subset of another event's times for the same user.
– Ravichandran Jothi
Jan 2 at 11:12