Java or SQL add a missing months in array

Question

Good day, dear colleagues

Could you help me? I can't find a decision. I get array from MySQL that look like that (quantity and month):

[2, 07.16, 3, 08.16, 2, 10.16, 1, 11.16, 1, 12.16, 1, 01.17]

And I need to add 0 and months that are missed inside this period. For this array should be added 0 and 09.16 after 08.16, so to become look like:

[2, 07.16, 3, 08.16, 0, 09.16, 2, 10.16, 1, 11.16, 1, 12.16, 1, 01.17]

Will be grateful for any advice!

PS. I tryed to do something like this in Java:

for (int i = objArrayOfCalulatedRisks.length; i > 3; i = i - 2) {
            String dayMonthAndYear = objArrayOfCalulatedRisks[i].toString();
            StringBuilder sb = new StringBuilder();
            sb.append(dayMonthAndYear.charAt(3));
            sb.append(dayMonthAndYear.charAt(4));
            String rightMonth = sb.toString();
            String dayMonthAndYear2 = objArrayOfCalulatedRisks[i-2].toString();
            StringBuilder sb2 = new StringBuilder();
            sb2.append(dayMonthAndYear.charAt(3));
            sb2.append(dayMonthAndYear.charAt(4));
            String leftMonth = sb2.toString();
            int rightM = Integer.parseInt(rightMonth);
            int leftM = Integer.parseInt(leftMonth);        

            if (leftM + 1 != rightM) {

            }         

Answer 1

First you should parse your input array into something appropriate like a NavigabeMap<YearMonth, Integer>. Then you can calculate the months between the lowest and the highest YearMonths and fill your result array.

String[] objs = { "2", "07.16", "3", "08.16", "2", "10.16", 
                  "1", "11.16", "1", "12.16", "1", "01.17" };
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("MM.yy");
NavigableMap<YearMonth, Integer> quantities = new TreeMap<>();
for (int i = 0; i < objs.length; i += 2) {
  quantities.put(YearMonth.from(formatter.parse(objs[i + 1])), Integer.valueOf(objs[i]));
}
String[] result;
if (quantities.isEmpty()) {
  result = new String[0];
} else {
  YearMonth lowest = quantities.firstKey();
  YearMonth highest = quantities.lastKey();
  int months = (int) ChronoUnit.MONTHS.between(lowest, highest) + 1;
  result = new String[months * 2];
  for (int i = 0; i < months; i++) {
    YearMonth ym = lowest.plusMonths(i);
    result[i * 2] = quantities.getOrDefault(ym, 0).toString();
    result[i * 2 + 1] = formatter.format(ym);
  }
}
System.out.println(Arrays.toString(result));

---

Output:

[2, 07.16, 3, 08.16, 0, 09.16, 2, 10.16, 1, 11.16, 1, 12.16, 1, 01.17]

If you're looking for an MySQL solution then you should use a number table.