Getting data for input date ranges on monthy basis

Question

In my Oracle table I have Date and Salary column. I have created an Java api which passes start date and end date. From my DB i need to fetch records with sum of Salary column Monthly basis. Ex : Input are :- start date : 5-Jan-2019 and end Date: 13-March-2019. It should return 3 records as :

Sum of Salary for 5-Jan-2019 to 4-Feb-2019,
Sum of Salary for 5-Feb-2019 to 4-Mar-2019,
sum of Salary for 5-March-2019 to 13-March-2019.

need help thanks in advance

What's the logic here? Does it always go in steps of one month from the start date? What if start date is January 31 for instance? — Alex, Jul 21 '20 at 07:23
Are you asking how to find the cut dates (5 Feb, 5 Mar) or how to retrieve data from Oracle? (Asking both would be too much for one Stack Overflow question, then you’d need to ask two questions.) — Ole V.V., Jul 21 '20 at 08:22
Ole, cut dates are given, we need to treat the given date as start data of billing cycle . — Neeraj, Jul 22 '20 at 08:49
Alex, if start date will 31st Jan then end date should be 27/28th Feb based on year — Neeraj, Jul 22 '20 at 09:43

score 1 · Answer 1 · answered Jul 21 '20 at 08:35

1

Group rows based on distance from start date in months:

select sum(salary) -- probably also put some interval identification here
from t
where date_column between date '2019-01-05' and date '2019-03-13' -- put startDate and endDate here
group by trunc(months_between(date_column, date '2019-01-05')) -- put startDate here

answered Jul 21 '20 at 08:35

Tomáš Záluský

10,735
2
36
64

What does `trunc()` do? It’s been a while since I used Oracle. – Ole V.V. Jul 21 '20 at 08:37
@OleV.V. https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions200.htm – Tomáš Záluský Jul 21 '20 at 10:02
Hi Thomas, this solution works, but Months_between is not returning correct output when we need to get data from 28th Feb to 21 March . Because the diff returned by above method for 28-Feb- to 29-March is 1, but ideally it should be '0'. Because next cycle starts 30th March – Neeraj Jul 24 '20 at 10:35
@Neeraj what date in March you actually mean? (you typed 21, then 29) Anyway, both dates produce correct result - `select trunc(months_between(date '2019-02-28', date '2019-03-29')), trunc(months_between(date '2019-02-28', date '2019-03-21')) from dual;` If it does not suit your need, it will probably be better to create own my_months_between function rather than hack some corner cases directly into SQL. – Tomáš Záluský Jul 24 '20 at 11:00
@Tomas, it was a typo . Let me explain this with below queries : select FLOOR(MONTHS_BETWEEN ( date '2018-03-27',date '2018-02-28' )) from dual o/p : Current Month select FLOOR(MONTHS_BETWEEN ( date '2018-03-28',date '2018-02-28' )) from dual o/p : Next Month . But what I need is my cycle should be same till 28th-Feb to 29Mar. These are some corner cases for when start date is some last date of any month – Neeraj Jul 24 '20 at 13:53
@Neeraj I don't clearly understand what is your expected output from each of stated three cases but I believe it should be solvable using simple arithmetic. – Tomáš Záluský Jul 24 '20 at 14:52
@Tomas, let me make it more clear, My requirement is to calc the billing cycle, Ex : If start : 30-Jan-2018 to 28-Feb-2018. Then output should be two cycles : 30Jan to 27 Feb and second one 28-feb to remaining days. If Start date : 31-March-2018 to 31-May-2018, then o/p should be three cycles : 31-March-2018 to 29-April-2018, second ccycle: 30-April-2019 to 30-May-2019 and third one be 31-May to remaning days. But Months_between() is not returning this diff, I am expecting soln for above corner cases along with general (like for i/p dates :14th to next month 13th, 25th to next month 24 ) – Neeraj Jul 24 '20 at 15:21

score 1 · Answer 2 · answered Jul 21 '20 at 11:14

1

I would just subtract 4 days and extract the month:

select trunc(date - interval 4 day, 'MON') as yyyymm, sum(salary)
from t
group by trunc(date - interval 4 day, 'MON')
order by min(date);

trunc() with the 'MON' argument truncates the date to the beginning of the month. You could also convert the value to a string:

select to_char(date - interval 4 day, 'YYYY-MM') as yyyymm, sum(salary)
from t
group by to_char(date - interval 4 day, 'YYYY-MM')
order by min(date);

answered Jul 21 '20 at 11:14

Gordon Linoff

1,242,037
58
646
786

Hi Gordan , thanks for the solution, this is working except when start day is 29th Jan, 30th Jan, 31st Jan. This is the issue I am facing this approach – Neeraj Jul 22 '20 at 09:42
@Neeraj . . . The code is *subtracting* four days. I don't see how end of month would have any affect on this. But perhaps you should ask a *new* question and clarify what is supposed to happen for different start dates. This seems to be pretty clearly about subtracting four days. – Gordon Linoff Jul 22 '20 at 11:00
since I am expecting the monthly cycle, so for these month end dates cycle would be different – Neeraj Jul 24 '20 at 10:32

score 0 · Answer 3 · answered Jul 21 '20 at 08:36

java.time

How to find the cut dates:

    DateTimeFormatter inputFormatter = DateTimeFormatter.ofPattern("d-[MMMM][MMM]-uuuu", Locale.ENGLISH);
    
    String inputStartDate = "5-Jan-2019";
    String inputEndDate = "13-March-2019";
    
    LocalDate startDate = LocalDate.parse(inputStartDate, inputFormatter);
    LocalDate endDate = LocalDate.parse(inputEndDate, inputFormatter);
    
    LocalDate[] cutDates = startDate.datesUntil(endDate, Period.ofMonths(1))
            .toArray(LocalDate[]::new);
    
    System.out.println("Cut dates: " + Arrays.toString(cutDates));

Output is:

Cut dates: [2019-01-05, 2019-02-05, 2019-03-05]

Now you can query your Oracle database about each interval from one cut date inclusive to the next cut date exclusive, and finally from the last cut date to your end date (inclusive or exclusive depending on your requirements).

The result may surprise a bit if start date is 29, 30 or 31 of a month. For example:

    String inputStartDate = "31-Jan-2019";
    String inputEndDate = "9-April-2019";

Cut dates: [2019-01-31, 2019-02-28, 2019-03-31]

One challenge was parsing your dates since it seems you sometimes get a month abbreviation (Jan rather than January), sometimes a full month name (March, not Mar). The square brackets in the format pattern string denote optional parts, so our formatter will accept either a full month name through MMMM or an abbreviation through MMM.

For how to pass your LocalDate objects to SQL see for example the link at the bottom.

Links

Oracle tutorial: Date Time explaining how to use java.time.
Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2

Hi Ole, thanks for the approach you shared, thing is I am on Oracle part with these date, so cant use Java for translation. Getting Start date and end date in DB, inside DB i have write any logic — Neeraj, Jul 22 '20 at 09:58

Getting data for input date ranges on monthy basis

3 Answers3

java.time

Links