I've been searching the forums for cool data logic, but haven't found exactly what I'm looking for. I need to do the following 2 things (in different SQL's).
(1) Find the PREVIOUS Oct-1. So if it is Oct-1 or later, I want this year. If it is Jan-1 through Sep-30, I want last year. I'll be using this in a WHERE clause
AND a.process_date > xxxx (xxxx is the previous Oct-1)
I could always use a CASE statement to extract the current month and have 2 different selects depending on if it is Jan-Sep or Oct-Dec.
(2) Find all data from the previous month. So if I'm runnig the SQL in the month of July 2011 (doesn't matter what day) then I would get everything between and including Jun-1, 2011 and Jun-30, 2011. This would also be used in a WHERE clause
AND a.process_date BEWTEEN xxxx AND yyyy (xxxx is the 1st day of last month and yyyy is the last day of last month)
select calendar_date, case when cast(extract(year from current_date) !! '-10-01' as date)-calendar_date > 0 then cast((extract(year from current_date)-1) !! '-10-01' as date) else cast(extract(year from current_date) !! '-10-01' as date) end from sys_calendar.calendar where current_date = calendar_date or calendar_date = add_months(current_date, 4)