How to display in sql records of past Month (not last 30 days) and update them every 2nd calendar da-CodePudding

I am trying to represent data for the past MONTH so for example 30 days from April 2nd and continue to update data every month so that on May 2nd, data shows for the 30 days prior to May 2nd.

I will show my current code:

SELECT 'Working Equipment' label, count(*) FROM   LOGS_HISTORY
WHERE  MODE__ = 'Working'
AND    TIME_ >= add_months(sysdate, -1)
union all
SELECT 'Standby Equipment' label, count(distinct TAG_IDENTIFIER) FROM   LOGS_HISTORY
WHERE  MODE__ = 'Standby'
AND    TIME_ >= add_months(sysdate, -1)

It shows data 30 days prior to sysdate, please look through it and advise what should I change?

CodePudding user response：

For your requirement you can use this:

AND TIME_ >= add_months(trunc(sysdate-1,'MM'),-1)

This example query shows how the start date changes from 1st March to 1st April on 2nd May:

with dates as
( select trunc(date '2022-04-27') rownum d
  from dual
connect by level < 10
)
select d as sys_date, add_months(trunc(d-1,'MM'),-1) from_date
from dates
order by 1;

Result:

SYS_DATE                FROM_DATE
--------                ---------
28-APR-2022 00:00:00    01-MAR-2022 00:00:00
29-APR-2022 00:00:00    01-MAR-2022 00:00:00
30-APR-2022 00:00:00    01-MAR-2022 00:00:00
01-MAY-2022 00:00:00    01-MAR-2022 00:00:00
02-MAY-2022 00:00:00    01-APR-2022 00:00:00
03-MAY-2022 00:00:00    01-APR-2022 00:00:00
04-MAY-2022 00:00:00    01-APR-2022 00:00:00
05-MAY-2022 00:00:00    01-APR-2022 00:00:00
06-MAY-2022 00:00:00    01-APR-2022 00:00:00