Getting Table Results based on a Start and End Date using a single date












0














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.



Example Data










share|improve this question
























  • 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
















0














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.



Example Data










share|improve this question
























  • 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














0












0








0







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.



Example Data










share|improve this question















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.



Example Data







sql oracle date point-in-time






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 15 '18 at 7:23









a_horse_with_no_name

292k46446541




292k46446541










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


















  • 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












2 Answers
2






active

oldest

votes


















0














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





share|improve this answer





























    0














    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')





    share|improve this answer





















      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      0














      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





      share|improve this answer


























        0














        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





        share|improve this answer
























          0












          0








          0






          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





          share|improve this answer












          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






          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 16 '18 at 3:06









          Jeffrey KempJeffrey Kemp

          47.8k1187132




          47.8k1187132

























              0














              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')





              share|improve this answer


























                0














                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')





                share|improve this answer
























                  0












                  0








                  0






                  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')





                  share|improve this answer












                  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')






                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 15 '18 at 4:51









                  BrekhnaaBrekhnaa

                  363




                  363






























                      draft saved

                      draft discarded




















































                      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.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • 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.




                      draft saved


                      draft discarded














                      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





















































                      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







                      Popular posts from this blog

                      Guess what letter conforming each word

                      Port of Spain

                      Run scheduled task as local user group (not BUILTIN)