Quick background: My employer uses a rather odd (to others, it seems) date range for our Fiscal months - the 22nd to the 21st of each month. For example, Fiscal February runs 01.22.2012 to 02.21.2012.
Up until this point I’ve been able to use SYSDATE and simple functions to calculate dates in my reporting - they’ve just been the previous day, or previous 7 days, etc.
Now I need to work on reports that have this dynamic date range, and I’m not sure where to start.
What I’m looking for is something that will look at today’s date (03.02.2012, for example), and logically determine that the current fiscal month (and thus date range for the report) is 02.22.2012 - 03.21.2012. One of the nasty caveats to this is that on 03.22.2012, thereport range needs to stay 02.22.2012 - 03.21.2012, and it’s not until the 23rd that it would move on to the next fiscal month.
There is probably a better way to do this and I am not 100% sure it works the way you want but…
ReportDate between to_date(concat(to_char(add_months(sysdate,-1),‘MM’), concat(‘22’,to_char(add_months(sysdate,-1),‘YYYY’))),‘MM-DD-YYYY’) and to_date(concat(to_char(sysdate,‘MM’), concat(‘22’,to_char(sysdate,‘YYYY’))),‘MM-DD-YYYY’)
I assumed that on the 22nd of the month you still needed the previous fiscal month
You are correct sir, your 2nd example is exactly what I need to have happen. My question is this, will it still work correctly on months with different lengths, or across a year? For example, Fiscal January '12 was 12.22.2011 - 01.21.2012.
Now, for the fun part (and it may be because it's a Friday and my brain is a bit tired, but...), how do I integrate a CASE statement into a WHERE clause? This is the actual date select code as it stands currently:
AND W.WFINDATE BETWEEN DATE '2012-02-22' AND DATE '2012-02-29'
And do I need to wrap the output in a to_char in order to put it in the format above?
jacquesrk wrote: N.B., If you use Dave - Stt's formula
for a sysdate of 2012-03-22 you get
begin date=2012-02-22
end date=2012-03-22
for a sysdate of 2012-03-23 you also get
begin date=2012-02-22
end date=2012-03-22
If you use the formula in my previous post
for a sysdate of 2012-03-22 you get
begin date=2012-02-22
end date=2012-03-21
for a sysdate of 2012-03-23 you get
begin date=2012-03-22
end date=2012-04-21
I'm not sure which one is the right one for you, but I believe it's the second one.
jacquesrk - you're awesome. I've thrown every test case at your solution that I can think of (replacing SYSDATE with DATE 'YYYY-MM-DD' to simulate dates) and I haven't been able to break it. This problem has been vexing me for well over two years.
Debbie - I was never quite able to figure out how to integrate that into my code, and to make sure that it rolled forwarded as I needed it to. That, and I really need most of my solutions to be client neutral in the horrible event we decide to forgo our enterprise license at some point.
Also, I did determine how to work this into my WHERE clause as a sub-SELECT in case anyone else needs to see it:
SELECT ACCTCORP, WJOB, COUNT(*)
FROM IDST_WIP
WHERE WFINDATE BETWEEN (SELECT CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) < 23
THEN TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
ELSE TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYYMM') || '22', 'YYYYMMDD')
END AS FISCALSTART
FROM DUAL)
AND (SELECT CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, 'DD')) < 23
THEN TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
ELSE TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), 'MM'), 'YYYYMM') || '21', 'YYYYMMDD')
END AS FISCALEND
FROM DUAL)
AND ACCTCORP = 09579
GROUP BY ACCTCORP, WJOB
Hello N.B., to answer your questions:
as your tests show, the expression will return the desired result on months with different lengths or across a year (that’s because we use the Oracle ADD_MONTHS function, which will take care of those details for us)
to use it in a where clause, you can skip the “select … from dual” and put the expression directly in the where clause, as long as you remember to remove the column alias.
Meaning, my expression said CASE … END AS column_alias
remove the AS column_alias and you can put the expression in a where clause, like this:
WHERE WFINDATE BETWEEN (CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, ‘DD’)) < 23
THEN TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, -1), ‘MM’), ‘YYYYMM’) || ‘22’, ‘YYYYMMDD’)
ELSE TO_DATE(TO_CHAR(TRUNC(SYSDATE, ‘MM’), ‘YYYYMM’) || ‘22’, ‘YYYYMMDD’)
END)
AND (CASE
WHEN TO_NUMBER(TO_CHAR(SYSDATE, ‘DD’)) < 23
THEN TO_DATE(TO_CHAR(TRUNC(SYSDATE, ‘MM’), ‘YYYYMM’) || ‘21’, ‘YYYYMMDD’)
ELSE TO_DATE(TO_CHAR(TRUNC(ADD_MONTHS(SYSDATE, 1), ‘MM’), ‘YYYYMM’) || ‘21’, ‘YYYYMMDD’)
END)
Forgot your third question about putting the CASE … END expression inside a TO_CHAR: don’t do that if WFINDATE is a column of DATE datatype. You want to compare like datatypes as much as possible. If WFINDATE is a DATE column then you should use the CASE … END expression as is, since that expression returns a DATE value.