get the data monthly wise between two dates in oraclesql

user_7622437 0
Viewed 65

I have table of hospital, in which I need to show the patient expenditure from this month to this month . Suppose I have dates eg. from date 01/05/2020 and to date 20/02/2020 I need monthly data between these two dates like from 01th JAN to 30th JAN one data and from 1st FEB to 20th FEB. Please suggest some query i trying it but not able to fetch between two dates.

example of the result set i need :

MONTH    PT_Expen
----------------
JAN-20    30000
FEB-20     50000
MAR-20     60000

this is my table structure

 PT_EXP PT_NAME CREATED_DATE
---------------------------------
 30000  JACK   07/01/2020 
 30000  SWETA  08/01/2020  
 30000  RAM    08/01/2020  
 40000  JOHN   01/02/2020
 60000  SIMON  10/03/2020
 70000  KIRA   11/04/2020

IF i give fromdate as JAN1 and todate as MAR24 i need the data from jan1 to jan 30 one data,feb1 to feb28 one data ,Mar1 to Mar24 one data and my date format :'dd/mm/yyyy'

datetime oracle sql
2 Answers

One option might be

SQL> with your_table (pt_exp, pt_name, created_date) as
  2    (select 30000, 'JACK' , date '2020-01-07' from dual union all
  3     select 30000, 'SWETA', date '2020-01-08' from dual union all
  4     select 30000, 'RAM'  , date '2020-01-08' from dual union all
  5     select 40000, 'JOHN' , date '2020-02-01' from dual union all
  6     select 60000, 'SIMON', date '2020-03-10' from dual union all
  7     select 70000, 'KIRA' , date '2020-04-11' from dual
  8    )
  9  select to_char(created_date, 'yyyy.mm') month,
 10         sum(pt_exp) pt_expen
 11  from your_table
 12  where created_date between date '2020-01-05' and date '2020-02-20'
 13  group by to_char(created_date, 'yyyy.mm')
 14  order by 1;

MONTH     PT_EXPEN
------- ----------
2020.01      90000
2020.02      40000

SQL>
user_90979060

This is a job for TRUNC() and ADD_MONTHS().

Oracle's TRUNC() function works on datestamps, and they can be truncated to the nearest month. That is, TRUNC('2021-03-20', 'MM') gets you 2021-03-01. That means you can easily choose ranges of months and group by month. This example reports on the current (sysdate) calendar month and the two months preceding it.

SELECT TRUNC(datestamp, 'MM') AS month_beginning,
       patient,
       SUM(payment) AS total_payment,
       COUNT(*) AS number_of_payments
  FROM my_table 
 WHERE datestamp >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2)
   AND datestamp <  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1)
 GROUP BY patient, TRUNC(datestamp, 'MM')
 ORDER BY patient, TRUNC(datestamp, 'MM')

Edit If you want to specify a from date and a to date, and get the entire months containing those dates as well as the months between them, use this.

SELECT TRUNC(datestamp, 'MM') AS month_beginning,
       patient,
       SUM(payment) AS total_payment,
       COUNT(*) AS number_of_payments
  FROM my_table 
 WHERE datestamp >= TRUNC(start_date, 'MM')
   AND datestamp <  ADD_MONTHS(TRUNC(end_date, 'MM'), 1)
 GROUP BY patient, TRUNC(datestamp, 'MM')
 ORDER BY patient, TRUNC(datestamp, 'MM')
user_2056080
Related