WHERE LAST_UPDATE IS THE PREVIOUS QUARTER

[LisaLeach]

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]

Hi @LisaLeach 

I think we need more information to help out here. Which database are you connecting to (e.g. Snowflake/MySQL/Microsoft SQL Server)?
Also, as this is an Alteryx forum, are you using this SQL in a workflow/tool? If so which tool/where in your workflow?

You say you're getting errors. What do the errors say?

Ollie

[LisaLeach]

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.

[dreldrel]

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

[OllieClarke]

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

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