This week's giveaway is in the Testing forum.
We're giving away four copies of TDD for a Shopping Website LiveProject and have Steven Solomon on-line!
See this thread for details.
Win a copy of TDD for a Shopping Website LiveProject this week in the Testing forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Liutauras Vilda
  • paul wheaton
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Carey Brown
  • Frits Walraven
Bartenders:
  • Piet Souris
  • Himai Minh

How to use a while loop to return the appointments for the next seven days.

 
Ranch Hand
Posts: 153
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a requirement to show a person's appointments for the next seven days. So if today is Tuesday I need to show Wednesday to Tuesday.

The table I am sourcing has the patient's recurring appointments with:
  • Start date
  • End date (if null does not end)
  • sunday (Y or N)
  • monday (Y or N)
  • tuesday (Y or N)
  • wednesday (Y or N)
  • thursday (Y or N)
  • friday (Y or N)
  • saturday (Y or N)


  • So the extract on a Tuesday 28/12/2021 could have:
  • 2020-10-28
  • null
  • N
  • N
  • Y
  • N
  • Y
  • Y
  • N

  • The output would be:
  • Wed - N
  • Thu - Y
  • Fri - Y
  • Sat - N
  • Sun - N
  • Mon - N
  • Tue - Y


  • However, if there is an end date (end date is exclusive) and a new series starts then I could have:
  • 2020-10-28
  • 2021-12-31
  • N
  • N
  • Y
  • N
  • Y
  • Y
  • N
  • 2021-12-31
  • null
  • N
  • Y
  • N
  • N
  • N
  • N
  • Y

  • The output would be:
  • Wed - N
  • Thu - Y
  • Fri - N
  • Sat - Y
  • Sun - N
  • Mon - Y
  • Tue - N


  • I am using a while (result.next()){...} to get each row returned. I am having difficulty in working out the logic within this while loop to return the required results.

    Kind regards,

    Glyn
     
    Rancher
    Posts: 4739
    38
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Can you post the code that you are working on? Be sure to wrap the code in code tags: Select the code and press the Code button.
     
    Glyndwr Bartlett
    Ranch Hand
    Posts: 153
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    The trouble is that I have not been able to work out anything that makes sense. However, this is what I have:





    Still very rough; however, working on it. Any help would be appreciated.

    Kind regards,

    Glyn
     
    Norm Radder
    Rancher
    Posts: 4739
    38
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    One of the steps a programmer takes before writing code is to design it.
    Can you describe the steps the program needs to take to solve the problem?
    What data does the program have at the start?  You listed one example.

    I don't know SQL so I'll leave that part to someone else.
     
    Saloon Keeper
    Posts: 9266
    78
    Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Note that in the worst case scenario you would have to query the database 7 separate times with each entry only covering a single day.
     
    Glyndwr Bartlett
    Ranch Hand
    Posts: 153
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Only one SQL query required and that works, so no need to change the SQL. I provided the SQL for information only.

    I have given two examples of what the SQL can return with an example of the output of the code that operates on it.  I will explain it in another way.

    The fields returned are:



    The first example returned by the SQL is:



    The code run against this data is run on a Tuesday so needs to return:



    Another example is that the SQL returns:



    The code run against this data is run on a Tuesday so needs to return:

    Wed, N, Thu, Y, Fri, N, Sat, Y, Sun, N, Mon, Y, Tue, N

    I suppose a third example would be if we have three start and end:



    The code run against this data is run on a Tuesday so needs to return:



    The design is:
  • Determine the start date (in this example the code is run on a Tuesday so the first day returned is a Wednesday)
  • Return each day and Y/N until the end date on the row is reached

  • In the first example there is only one row without an end date so read stt_wednesday N, stt_thursday Y, stt_friday Y, stt_saturday N, stt_sunday N, stt_monday N, stt_tuesday Y

    In the second example there are two rows with end/start dates that are equal so from the first row read stt_wednesday N, stt_thursday Y. Friday is on the 31/12/2021 which is the end date of this row so read the next row. The next row starts on the 31/12/2021 and has no end date so read  stt_friday N, stt_saturday Y, stt_sunday N, stt_monday Y, stt_tuesday N

    In the third example there are three rows with end/start dates equal so from the first row read stt_wednesday N, stt_thursday Y. Friday is on the 31/12/2021 which is the end date of this row so read the next row.
    The next row starts on the 31/12/2021 and has an end date or 02/01/2022 so read stt_friday N, stt_saturday Y, . Sunday is on the 02/01/2022 which is the end date so read the next row.
    The next row starts on 02/01/2022 and has no end date so read stt_sunday N, stt_monday Y, stt_tuesday N.

    In this example:
  • The program was run on a Tuesday so the returned start date was a Wednesday; however, the program can be run on any day.
  • The end date of one row was the same date as the start date of the next row. It is possible that the next start date could start later. Therefore, any days between the end date and start date are assigned "N". For example Row has an end date of 31/12/2021 (Friday) the next row has a start date of 03/01/2021 (Monday). Therefore, Friday, Saturday and Sunday are assigned "N" each and the Monday and Tuesday are assigned the values returned in the row (stt_monday and stt_tuesday).


  • I hope I have explained it as clearly as possible. This is very complicated which I why I am requesting help (I am not the smartest person in the room).

    Kind regards,

    Glyn
     
    Carey Brown
    Saloon Keeper
    Posts: 9266
    78
    Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    In this example you'd have a start date <= 2021-10-28 and an end date > 2021-10-28 as your first query. You can't use an end date that's further out than that because there may only be a single entry for the 2021-10-28 date. In other words, if you're looking for a week's worth of entries you can't say end date > 2021-11-04 because you would have missed the only entry for 2021-10-28.

    Admittedly, not a likely scenario but the programmer must take all possibilities into account.
     
    Carey Brown
    Saloon Keeper
    Posts: 9266
    78
    Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    In your other example:
    Suppose you were looking for the week beginning 2020-12-29 which would go through 2021-01-04 (inclusive). So a query of start date <= 2020-12-29 would only yield one of these records but if you add to that the constraint that the end date be > 2021-01-04 then even that record would not be found.

    You only provided the SQL for information "but it works". You've left out the code and logic of how you are populating the start and end dates for the query. As it stands I don't see how it could work.
     
    Glyndwr Bartlett
    Ranch Hand
    Posts: 153
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Hi Carey,

    Thank you so much. You are right. There should be only one line checking the stt date "AND stt_end_date IS NULL || stt_end_date > ? " where ? is today's date.

    Kind regards,

    Glyn
     
    Carey Brown
    Saloon Keeper
    Posts: 9266
    78
    Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
     
    Carey Brown
    Saloon Keeper
    Posts: 9266
    78
    Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Now that I understand your problem better I can see how your SQL would likely work.
    For these two lines both question marks would need to be passed the same date that you're trying to get the Y/N for. The found record may cover additional dates or another query may have to be executed. Again, worst case you'll have to execute 7 queries, but nothing wrong with that.
     
    Glyndwr Bartlett
    Ranch Hand
    Posts: 153
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thank you so much Carey. It will take me some time to understand this code as I am not very proficient.

    Kind regards,

    Glyn
     
    Carey Brown
    Saloon Keeper
    Posts: 9266
    78
    Eclipse IDE Firefox Browser MySQL Database VI Editor Java Windows
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Let me  know if you have any questions.
     
    Getting married means "We're in love, so let's tell the police!" - and invite this tiny ad to the wedding:
    Free, earth friendly heat - from the CodeRanch trailboss
    https://www.kickstarter.com/projects/paulwheaton/free-heat
    reply
      Bookmark Topic Watch Topic
    • New Topic