I am new to Java JPA - Spring Boot. I want to create a JPA lookup that will count the number of rows between two dates.
I need to get a number that shows the number of members that have joined in the last 28 days. So like 10.
But also get a value that indicates the difference from last month -- so +4% -- or -2% -- so I suppose it will be a count1/count2 * 100 = difference. How would you detect the polarity -- so assess if its negative or positive?
Currently I have something like this
long countByRegisteredDateAfter(Date thresholdDate) throws Exception;
but need something maybe more like this
long countByRegisteredBeforeDateAfter(Date thresholdDate1, Date thresholdDate2)
throws Exception;
and maybe something more fine tuned like this
long countByRegisteredBeforeAndDateAfterAndRole(Date thresholdDate1, Date thresholdDate2, String role)
throws Exception;
Code so far:
// 28 days ago
Calendar thresholdPast28 = Calendar.getInstance();
thresholdPast28.set(Calendar.HOUR_OF_DAY,0);
thresholdPast28.set(Calendar.MINUTE,0);
thresholdPast28.set(Calendar.SECOND,0);
thresholdPast28.add(Calendar.DATE,-28);
java.util.Date thresholdPast28Date = thresholdPast28.getTime();
Long countLast28Days = (Long) tblLoginRepository.countByRegisteredDateAfter(thresholdPast28Date);
System.out.println("countLast28Days " + countLast28Days);
// 56 days ago
Calendar thresholdPast56 = Calendar.getInstance();
thresholdPast56.set(Calendar.HOUR_OF_DAY,0);
thresholdPast56.set(Calendar.MINUTE,0);
thresholdPast56.set(Calendar.SECOND,0);
thresholdPast56.add(Calendar.DATE,-28);
java.util.Date thresholdPast56Date = thresholdPast56.getTime();
Long countLast56Days = (Long) tblLoginRepository.countByRegisteredDateAfter(thresholdPast56Date);
System.out.println("countLast56Days " + countLast56Days);