Getting Table Results based on a Start and End Date using a single date
Currently I am having trouble trying to query a data set that contains multiple records for unique customers. Each row has a start date and end date and the most recent record marked as Most Current and end date as 12/31/9999. I am trying to write a query so that I can return all records that fall on a specific date such as months end (10/31/2018). My goal in Oracle SQL Developer is to type in all records/data on the day 10/31/2017 so the rows with the * are the only ones Id want back. The final outcome is to do this with a much larger file and see the entire year at months end where each person was at based on the month end date for 1 year.
sql oracle date point-in-time
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
add a comment |
Currently I am having trouble trying to query a data set that contains multiple records for unique customers. Each row has a start date and end date and the most recent record marked as Most Current and end date as 12/31/9999. I am trying to write a query so that I can return all records that fall on a specific date such as months end (10/31/2018). My goal in Oracle SQL Developer is to type in all records/data on the day 10/31/2017 so the rows with the * are the only ones Id want back. The final outcome is to do this with a much larger file and see the entire year at months end where each person was at based on the month end date for 1 year.
sql oracle date point-in-time
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
It was unclear what your requirements were because your sample data does not include examples that cover the edge cases. Technically, Brekhnaa's answer is correct because it gives the results you marked as desired. My answer was correct because it happens to match your intended meaning. You should have included at least one example record where the End date was not 12/31/9999 but that was intended to be included in the result set.
– Jeffrey Kemp
Dec 28 '18 at 6:12
add a comment |
Currently I am having trouble trying to query a data set that contains multiple records for unique customers. Each row has a start date and end date and the most recent record marked as Most Current and end date as 12/31/9999. I am trying to write a query so that I can return all records that fall on a specific date such as months end (10/31/2018). My goal in Oracle SQL Developer is to type in all records/data on the day 10/31/2017 so the rows with the * are the only ones Id want back. The final outcome is to do this with a much larger file and see the entire year at months end where each person was at based on the month end date for 1 year.
sql oracle date point-in-time
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
Currently I am having trouble trying to query a data set that contains multiple records for unique customers. Each row has a start date and end date and the most recent record marked as Most Current and end date as 12/31/9999. I am trying to write a query so that I can return all records that fall on a specific date such as months end (10/31/2018). My goal in Oracle SQL Developer is to type in all records/data on the day 10/31/2017 so the rows with the * are the only ones Id want back. The final outcome is to do this with a much larger file and see the entire year at months end where each person was at based on the month end date for 1 year.
sql oracle date point-in-time
sql oracle date point-in-time
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
edited Nov 15 '18 at 7:23
a_horse_with_no_name
300k46458551
300k46458551
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
asked Nov 15 '18 at 0:34
Pythonnoob12Pythonnoob12
33
33
It was unclear what your requirements were because your sample data does not include examples that cover the edge cases. Technically, Brekhnaa's answer is correct because it gives the results you marked as desired. My answer was correct because it happens to match your intended meaning. You should have included at least one example record where the End date was not 12/31/9999 but that was intended to be included in the result set.
– Jeffrey Kemp
Dec 28 '18 at 6:12
add a comment |
It was unclear what your requirements were because your sample data does not include examples that cover the edge cases. Technically, Brekhnaa's answer is correct because it gives the results you marked as desired. My answer was correct because it happens to match your intended meaning. You should have included at least one example record where the End date was not 12/31/9999 but that was intended to be included in the result set.
– Jeffrey Kemp
Dec 28 '18 at 6:12
It was unclear what your requirements were because your sample data does not include examples that cover the edge cases. Technically, Brekhnaa's answer is correct because it gives the results you marked as desired. My answer was correct because it happens to match your intended meaning. You should have included at least one example record where the End date was not 12/31/9999 but that was intended to be included in the result set.
– Jeffrey Kemp
Dec 28 '18 at 6:12
It was unclear what your requirements were because your sample data does not include examples that cover the edge cases. Technically, Brekhnaa's answer is correct because it gives the results you marked as desired. My answer was correct because it happens to match your intended meaning. You should have included at least one example record where the End date was not 12/31/9999 but that was intended to be included in the result set.
– Jeffrey Kemp
Dec 28 '18 at 6:12
add a comment |
2 Answers
2
active
oldest
votes
I think you want to list any records that are current as of a specified date. To do this I'd simply use a BETWEEN condition:
select * from mytable
where date'2017-31-10' between start and end
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
add a comment |
Can you elaborate what output you are expecting? What i can see is that you are trying to implement temporal functionality here. Kindly check this link if this serves your purpose
Oracle temporal
.
As for highlighted records, you can use a simple condition in where clause like this and format date part as required:
select * from emp where endd=to_date('9999/12/31','yyyy/mm/dd')
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
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%2f53310824%2fgetting-table-results-based-on-a-start-and-end-date-using-a-single-date%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think you want to list any records that are current as of a specified date. To do this I'd simply use a BETWEEN condition:
select * from mytable
where date'2017-31-10' between start and end
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
add a comment |
I think you want to list any records that are current as of a specified date. To do this I'd simply use a BETWEEN condition:
select * from mytable
where date'2017-31-10' between start and end
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
add a comment |
I think you want to list any records that are current as of a specified date. To do this I'd simply use a BETWEEN condition:
select * from mytable
where date'2017-31-10' between start and end
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
I think you want to list any records that are current as of a specified date. To do this I'd simply use a BETWEEN condition:
select * from mytable
where date'2017-31-10' between start and end
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
answered Nov 16 '18 at 3:06
Jeffrey KempJeffrey Kemp
48.3k1189134
48.3k1189134
add a comment |
add a comment |
Can you elaborate what output you are expecting? What i can see is that you are trying to implement temporal functionality here. Kindly check this link if this serves your purpose
Oracle temporal
.
As for highlighted records, you can use a simple condition in where clause like this and format date part as required:
select * from emp where endd=to_date('9999/12/31','yyyy/mm/dd')
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
add a comment |
Can you elaborate what output you are expecting? What i can see is that you are trying to implement temporal functionality here. Kindly check this link if this serves your purpose
Oracle temporal
.
As for highlighted records, you can use a simple condition in where clause like this and format date part as required:
select * from emp where endd=to_date('9999/12/31','yyyy/mm/dd')
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
add a comment |
Can you elaborate what output you are expecting? What i can see is that you are trying to implement temporal functionality here. Kindly check this link if this serves your purpose
Oracle temporal
.
As for highlighted records, you can use a simple condition in where clause like this and format date part as required:
select * from emp where endd=to_date('9999/12/31','yyyy/mm/dd')
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
Can you elaborate what output you are expecting? What i can see is that you are trying to implement temporal functionality here. Kindly check this link if this serves your purpose
Oracle temporal
.
As for highlighted records, you can use a simple condition in where clause like this and format date part as required:
select * from emp where endd=to_date('9999/12/31','yyyy/mm/dd')
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
answered Nov 15 '18 at 4:51
BrekhnaaBrekhnaa
363
363
add a comment |
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%2f53310824%2fgetting-table-results-based-on-a-start-and-end-date-using-a-single-date%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
It was unclear what your requirements were because your sample data does not include examples that cover the edge cases. Technically, Brekhnaa's answer is correct because it gives the results you marked as desired. My answer was correct because it happens to match your intended meaning. You should have included at least one example record where the End date was not 12/31/9999 but that was intended to be included in the result set.
– Jeffrey Kemp
Dec 28 '18 at 6:12