get the data monthly wise between two dates in oraclesql-CodePudding

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'

CodePudding user response：

One option might be

select to_char(datum, 'yyyy.mm') month,
       sum(money) pt_expen
from your_table
where datum between date '2020-05-01' and date '2020-02-20'
group by to_char(datum, 'yyyymm')
order by 1

CodePudding user response：

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 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')