-4

I have two tables Previous_Schedule and New_Schedule. Both the tables have 3 columns :
Objective_ID , START_DATE and END_DATE.

I need to create a NOT_PRESENT_IN_PREVIOUS_SCHEDULE Table having 3 columns :
Objective_ID , START_DATE and END_DATE.

If Previous Schedule is having sample data as:

Objective_id --Start_Date -- End_Date
1 -- 10-Jan-2014 -- 20-Jan-2014

If New_Schedule is having sample data as:

Objective_id -- Start_Date -- End_Date
1 -- 12-Jan-2014 -- 15-Jan-2014

My NOT_PRESENT_IN_PREVIOUS_SCHEDULE should have the following data based on the above scenario:

Objective_id -- Start_Date --End_Date
1 -- 10-Jan-2014 -- 11-Jan-2014
1 -- 16-Jan-2014 -- 20-Jan-2014

The logic having the NOT_PRESENT_IN_PREVIOUS_SCHEDULE output should be implemented in Java. It should be generic for any sort of PREVIOUS_SCHEDULE and NEW_SCHEDULE as an Input returning NOT_PRESENT_IN_PREVIOUS_SCHEDULE as an output.

Shashwat Kumar
  • 5,159
  • 2
  • 30
  • 66
Anonymous
  • 1
  • 4
  • 4
    Is it a Java question, or a SQL question? If it's a Java question, why are you talking about tables? – JB Nizet Jun 10 '17 at 07:39
  • Can we assume there are no overlapping schedules within one of the tables? To put it the other way around, could new_schedule contain both 10-Jan through 21-Jan and 16-Jan through 23-Jan? – Ole V.V. Jun 10 '17 at 08:03
  • You should show us what you have tried. It will give us a much better picture of what you already know and where your issues are, and thus a much better starting point for guiding you. – Ole V.V. Jun 10 '17 at 08:05
  • It is a Java Question . The tables are representing the Bean Structure. – Anonymous Jun 10 '17 at 08:16
  • Make sure you get your rows in chronologically sorted order from each table. Merge the two: search for a merge algorithm, they are described in many places on the Internet. Drop the schedules that are fully covered by one ore more previous schedules, split those that are partly covered (as in your example) and include those unchanged that do not overlap with a previous schdule. It will take more lines of code than anyone should be writing for you. If you get stuck, please ask a more specific question about it. – Ole V.V. Jun 10 '17 at 08:17
  • The PREVIOUS_SCHEDULE start and end dates and the NEW_SCHEDULE start and end dates can be overlapping as well. I need to print the refreshed cancelled start date and end date pertaining to NOT_PRESENT_IN_PREVIOUS_SCHEDULE. – Anonymous Jun 10 '17 at 08:18
  • No database queries are to be used. The Date Comparison is to be done in Java. – Anonymous Jun 10 '17 at 08:20
  • I need the start date and end date. Assume the tables mentioned above stored in a List previous and List new . The Schedule bean is having following attributes : objective_id,startDate,endDate. I need to print a List output having the refreshed start date and end date based on the above logic mentioned in the output table – Anonymous Jun 10 '17 at 08:24
  • Excuse me, have you accidentally swapped? It seems your sample `NOT_PRESENT_IN_PREVIOUS_SCHEDULE` contains the dates that are not present in your **`New_Schedule`**?? – Ole V.V. Jun 10 '17 at 08:28
  • Yes. NOT_PRESENT_IN_PREVIOUS_SCHEDULE should contain the dates in PREVIOUS_SCHEDULE but not in NEW_SCHEDULE. Apology for the naming convention. – Anonymous Jun 10 '17 at 08:32
  • Can we ignore objective ID? Or should filtering happen based on it? – Ole V.V. Jun 10 '17 at 08:45
  • Objective ID is a common identifier amongst all the schedules. The output should have objective ID along with its refreshed start date. Many to many mapping are also possible. – Anonymous Jun 10 '17 at 08:48
  • I recommend you start by writing unit tests. One, they will be good for documenting the exact requirements. Two, your code will be complicated enough that you want to test it thoroughly. – Ole V.V. Jun 10 '17 at 08:50
  • 1
    @Anonymous Please edit your Question with additional info rather than posting as Comments. – Basil Bourque Jun 10 '17 at 17:02

2 Answers2

0

To convert the date strings into Java date objects, you could use the SimpleDateFormat class. Solution for Java 7:

String string = "20-Jan-2014";
DateFormat format = new SimpleDateFormat("dd-MMM-yyyy", Locale.ENGLISH);
Date date = format.parse(string);

For Java 8 you could find a solution here.

To calculate the difference between two dates, you could use this operation:

long diff = date2.getTime() - date1.getTime();
System.out.println ("Days: " + TimeUnit.DAYS.convert(diff, TimeUnit.MILLISECONDS));

Calculate the difference between Previous_Schedule and New_Schedule

An idea on how to solve this problem could be to transform the date period in Previous_Schedule into single dates, stored in a Set.

Set<String> dates = new HashSet<String>();
dates.add( "10-Jan-2014" );
dates.add( "11-Jan-2014" );
dates.add( "12-Jan-2014" );
...
dates.add( "20-Jan-2014" );

Then you remove the dates from the period in New_Schedule from the Set:

dates.remove( "12-Jan-2014" );
...
dates.remove( "15-Jan-2014" );

The remaining elements in the Set would provide the basis to create NOT_PRESENT_IN_PREVIOUS_SCHEDULE.

Instead of using Strings, you could add date objects to the Set as well:

Set<Date> dates = new HashSet<Date>();
dates.add( date1 );

How to split a date period like 10-Jan-2014 -- 20-Jan-2014 into single dates and how to do the reverse task to create NOT_PRESENT_IN_PREVIOUS_SCHEDULE should come from your own creativity. Hint: you might use a loop to solve that task.

LoHer
  • 172
  • 1
  • 13
  • I need the start date and end date. Assume the tables mentioned above stored in a List previous and List new . The Schedule bean is having following attributes : objective_id,startDate,endDate. I need to print a List output having the refreshed start date and end date based on the above logic mentioned in the output table. – Anonymous Jun 10 '17 at 08:23
  • You will find an idea on how to solve that problem in my answer (updated). Hope that will help you. Anyways the idea behind StackOverflow is not to develop ready to use solutions, but to give ideas on how to solve a problem. Hopefully I could help you for your homework or for whatever it is. – LoHer Jun 10 '17 at 09:54
0

Here’s my suggestion. The following method “subtracts” two lists of schedules by eliminating the date intervals from the first list that are in the second list. It uses a double loop where it first iterates over the schedules in the second list, those that should be subtracted. For each such schedule it subtracts it from each schedule from the first list, building a new list of the resulting schedules.

public static List<Schedule> scheduleListDiff(
        List<Schedule> schedules, List<Schedule> schedulesToExclude) {
    // eliminate dates from schedulesToExclude one schdule at a time
    for (Schedule toExclude : schedulesToExclude) {
        List<Schedule> result = new ArrayList<>();
        for (Schedule originalSchedule : schedules) {
            result.addAll(originalSchedule.notPresentIn(toExclude));
        }
        schedules = result;
    }
    return schedules;
}

You may call it this way

    List<Schedule> notPresentInPreviousSchedule 
            = scheduleListDiff(previousSchedules, newSchedules);

With the lists from your question the result is the desired

1 -- 10-Jan-2014 -- 11-Jan-2014
1 -- 16-Jan-2014 -- 20-Jan-2014

I have fitted the Schedule class with an auxiliary method notPresentIn() to perform the actual comparison:

/** @return a list of 0, 1 or 2 schedules with the dates from this schedule that are not in other */
List<Schedule> notPresentIn(Schedule other) {
    if (other.end.isBefore(start) || end.isBefore(other.start)) { // no overlap
        return Collections.singletonList(this);
    }
    // now we know there is an overlap
    List<Schedule> result = new ArrayList<>(2);
    if (start.isBefore(other.start)) { // need to include day/s from the first part of this
        // this bit must end the day before other.start
        result.add(new Schedule(objectiveId, start, other.start.minusDays(1)));
    }
    if (end.isAfter(other.end)) { // need day/s from the last part
        result.add(new Schedule(objectiveId, other.end.plusDays(1), end));
    }
    return result;
}

I have not tested thoroughly, there could easily be a bug somewhere, but I hope this gets you started.

I have not considered efficiency. If you have millions of schedules you may benefit from a more complicated algorithm that sorts the schedules chronologically first so you don’t need to compare every schedule from one list to every schedule of the other. With a few hundred schedules I heavily doubt that you need care.

I am using java.time.LocalDate for the dates in the Schedule class:

int objectiveId;
// dates are inclusive; end is on or after start
LocalDate start;
LocalDate end;

Edit: I ran my code on the sample data from the duplicate question find out cancelled period from given date. That sample has two new sample schedules within one previous schedule. So this previous schedule should be split up into three. The result was:

107 -- 10 May 2016 -- 11 May 2016
107 -- 14 May 2016 -- 15 May 2016
107 -- 19 May 2016 -- 20 May 2016

This works because each iteration in scheduleListDiff() uses the result from the previous iteration, so first the schedule is split into two, next iteration one of the two is further split.

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
  • Thanks for your solution. It will work for one-one and one-many but for many-many I doubt. This solution would act as a stepping stone for my actual solution. – Anonymous Jun 10 '17 at 12:58
  • Please explain what you mean by many-to-many here. If previous schedules contain 10 through 18 January *and* 12 through 20 January and new schedules contain 13 thorugh 14 January and 16 through 17 January, would that count as many-to-many? I designed my algorithm to cover this case. It should split up each of the original schedules in three. – Ole V.V. Jun 10 '17 at 13:06
  • 1
    Thanks for your efforts. I will get back to you if I have found any scenario that is missed. – Anonymous Jun 10 '17 at 13:53
  • Your solution has solved 90% of my problem statement. – Anonymous Jun 10 '17 at 13:55
  • Great. I hope you can work out the remaining 10 %. If not, Stack Overflow has room for more questions… – Ole V.V. Jun 10 '17 at 13:55