WHERE LAST_UPDATE IS THE PREVIOUS QUARTER

Question

I am currently using this in my WHERE Clause to pull records for the previous month.

where extract(year from GFTBPM_70872_APP.CRV_INV_HEADER.CREATED_ON) = extract(year from sysdate) And extract(month from GFTBPM_70872_APP.CRV_INV_HEADER.CREATED_ON) = extract(month from sysdate) - 1

It was successfully changed to previous week.

where extract(year from GFTBPM_70872_APP.CRV_INV_HEADER.LAST_UPDATED_ON) = extract(year from sysdate) And extract(day from GFTBPM_70872_APP.CRV_INV_HEADER.LAST_UPDATED_ON) = extract(day from sysdate) – 7

How would this be updated to look for the previous quarter?  I keep getting errors.

OllieClarke · Answer

@LisaLeach 
There is indeed another way. (from here: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROUND-and-TRUNC-Date-Functions.html)

where trunc(CREATED_ON,'Q') = trunc(dateadd(sysdate,,-3),'Q'))

This checks whether the quarter of Created_on is the same as the quarter of 3 months ago

Hope that helps,

Ollie

dreldrel · Answer

Hi, could you please share more details about your error message or a sample?

LisaLeach · Answer

Hi.  It is an Oracle database. I was able to solve it this way.

where (Extract(Year from h.CREATED_ON) = Extract(Year from SysDate ) and Extract(Month from h.CREATED_ON) = Extract(Month from SysDate) - 3)
or (Extract(Year from h.CREATED_ON) = Extract(Year from SysDate) and Extract(Month from h.CREATED_ON) = Extract(Month from SysDate) - 2)
or (Extract(Year from h.CREATED_ON) = Extract(Year from SysDate ) and Extract(Month from h.CREATED_ON) = Extract(Month from SysDate) - 1)

This gave me all the records that were created 3, 2 and 1 month ago.  The report is run the first of each quarter so this will work fine enough.  I wont work if I have to run it off cycle.

There must be another way.