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

[Glyndwr Bartlett]

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

[Norm Radder]

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]

The trouble is that I have not been able to work out anything that makes sense. However, this is what I have:

String selectQry = ("SELECT stt_start_date, stt_end_date, stt_sunday, stt_monday, stt_tuesday, " +
		"stt_wednesday, stt_thursday, stt_friday, stt_saturday " +
" FROM csm_patient_session_type, csm_session_type, csm_sessiontimetable " +
" WHERE csm_patient_session_type.pat_id = ? " +
" AND csm_patient_session_type.st_id = csm_session_type.st_id " +
" AND csm_session_type.prm_id = ? " +
" AND st_archived IS NULL " +
" AND csm_session_type.st_id = csm_sessiontimetable.st_id " +
" AND stt_start_date <= ? " +
" AND (stt_end_date IS NULL || stt_end_date > ?) " +
" ORDER BY stt_start_date ASC;");

//Get the day of week
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
Date date = new Date();
String todaysDate = dateFormat.format(date);
Calendar c2 = Calendar.getInstance();
c2.setTime(date);
int dayOfWeek = c2.get(Calendar.DAY_OF_WEEK);//1 is Sunday

//Determine which day is the start day (i.e., today plus one)
if (dayOfWeek < 7) {
	dayOfWeek++;
}else {
	dayOfWeek = 1;
}

//Set up date increment
Date nextDay = new Date();
Calendar c1 = Calendar.getInstance(); 
c1.setTime(date); 

//Storage arrays for output
String[] days; //Starting day to seventh day
String[] daysYN; //Y or N for each day

while (result.next()) {
	
	Date endDate = null;
    if (result.getString("stt_end_date") != null) {
    	endDate = new SimpleDateFormat("yyyy-MM-dd").parse(result.getString("stt_end_date"));
    }else {
    	endDate = null;
    }
	
	if (dayOfWeek == 1) {//Need to replace this with a loop rather than check 1 to 7
		//Add Sunday and Y/N
		days[0] = "Sun";
		daysYN[0] = result.getString("stt_sunday");
		
		//Add 1 to today and check if < end date or end date is null
		c1.add(Calendar.DATE, 1);
        nextDay = c1.getTime();
        
		if (endDate.equals(null) || nextDay.before(endDate)) {//Need to loop through each day
			days[1] = "Mon";
			daysYN[1] = result.getString("stt_monday");
			
			c1.add(Calendar.DATE, 1);
	        nextDay = c1.getTime();
		}else {
			//Get next row and continue to loop
			//If there is not a next row then set remainder of days to "N".
		}
	}
}

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

Kind regards,

Glyn

[Norm Radder]

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.

[Carey Brown]

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]

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:

stt_start_date, stt_end_date, stt_sunday, stt_monday, stt_tuesday, stt_wednesday, stt_thursday, stt_friday, stt_saturday

The first example returned by the SQL is:

2020-10-28, null, N, N, Y, N, Y, Y, N

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

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

Another example is that the SQL returns:

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 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:

2020-10-28, 2021-12-31, N, N, Y, N, Y, Y, N
2021-12-31, 2022-01-02, N, Y, N, N, N, N, Y
2022-01-02, null, N, Y, N, N, N, N, Y

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

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]

2021-10-28, 2021-10-29, N, N, N, N, Y, Y, N
2021-10-29, 2021-11-01, N, Y, N, N, N, N, Y
2021-11-01, null, N, Y, N, N, N, N, Y

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]

In your other example:

2020-10-28, 2021-12-31, N, N, Y, N, Y, Y, N
2021-12-31, null, N, Y, N, N, N, N, Y

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]

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]

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

public class Appointments
{
	static final DateTimeFormatter	formatter = DateTimeFormatter.ISO_LOCAL_DATE;
	static final boolean	N = false;
	static final boolean	Y = true;
	
	List<DbRecord>	db = new ArrayList<>();
	DbRecord		currRecord = null;
	
	public static void main( String[] args )
	{
		new Appointments().run();
	}
	
	public void run()
	{
		initDb();
		LocalDate	date = LocalDate.parse( "2021-12-29" );
		for( int i=0 ; i < 7 ; i++, date = date.plusDays( 1L ) )
		{
			System.out.printf( "%s %d %s %c%n",
					format( date ),
					dayOfWeek( date ),
					Dy.values()[dayOfWeek(date)].toString(),
					dbIsAvailable( date ) ? 'Y' : 'N' );
		}
	}
	
	// Sunday = 0
	private int dayOfWeek( LocalDate date )
	{
		int		dow = date.getDayOfWeek().getValue();
		if( dow == 7 )
			dow = 0;
		return dow;
	}
	
	private boolean dbIsAvailable( LocalDate date )
	{
		if( ! recordContains( currRecord, date ) )
			currRecord = getRecord( date );
		return currRecord.isAvailable[ dayOfWeek( date ) ];
	}
	
	private boolean recordContains( DbRecord rec, LocalDate date  )
	{
		if( rec == null )
			return false;
		if( date.isBefore( rec.startDate ) )
			return false;
		return rec.endDate == null || date.isBefore( rec.endDate );
	}
	
	private DbRecord getRecord( LocalDate date )
	{
		for( DbRecord rec : db )
		{
			if( recordContains( rec, date ) )
			{
				System.out.println( "New record: " + rec );
				return rec;
			}
		}
		return null;
	}
	
	private static String format( LocalDate date )
	{
		return date.format( formatter );
	}
	
	private void initDb()
	{
		// Must be added in increasing order of start date
		// Records must not overlap start/end dates
		db.add( new DbRecord( "2020-10-28", "2021-12-31", N, N, Y, N, Y, Y, N ) );
		db.add( new DbRecord( "2021-12-31", null, N, Y, N, N, N, N, Y ) );
	}
}

enum Dy { SUN, MON, TUE, WED, THU, FRI, SAT };

class DbRecord
{
	static final DateTimeFormatter	formatter = DateTimeFormatter.ISO_LOCAL_DATE;
	LocalDate		startDate;
	LocalDate		endDate; // or null
	boolean[]		isAvailable = new  boolean[7]; // Sunday=0
	
	public DbRecord( String startDate, String endDate, boolean... isAvailable )
	{
		this.startDate = LocalDate.parse( startDate );
		this.endDate = endDate == null ? null : LocalDate.parse( endDate );
		for( int i=0 ; i < 7 ; i++ )
			this.isAvailable[i] = isAvailable[i];
	}
	
	public String toString()
	{
		return String.format( "%s, %s, %c, %c, %c, %c, %c, %c, %c",
				startDate.format( formatter ),
				endDate == null ? "null" : endDate.format( formatter ),
				isAvailable[0] ? 'Y' : 'N',
				isAvailable[1] ? 'Y' : 'N',
				isAvailable[2] ? 'Y' : 'N',
				isAvailable[3] ? 'Y' : 'N',
				isAvailable[4] ? 'Y' : 'N',
				isAvailable[5] ? 'Y' : 'N',
				isAvailable[6] ? 'Y' : 'N'
			);
	}
}

[Carey Brown]

Now that I understand your problem better I can see how your SQL would likely work.

" AND stt_start_date <= ? " +
" AND (stt_end_date IS NULL || stt_end_date > ?) " +

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]

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]

Let me  know if you have any questions.