Hi,
TRUNC (dt, 'Q') is useful for mapping all dates in a quarter to a single DATE, suitable for (e.g.) GROUP BY.
What if we're using a system with custom-defined quarters, that are NOT offset by a fixed number of months (or days) from the normal quarters? For example, say we defined the quarters as running from:
- February 2 though May 14 (inclusive),
- May 15 through July 31,
- August 1 through November 10, and
- November 11 through February 1.
How can we write something that acts like TRUNC (dt, 'Q'), but uses the quarters above, rather than the normal quarters?
I came up with the following query, but I feel there ought to be a better way:
SELECT dt
, TRUNC ( dt + CASE
WHEN TO_CHAR (dt, 'MM-DD') BETWEEN '02-02'
AND '03-31'
THEN 59
WHEN TO_CHAR (dt, 'MM-DD') BETWEEN '05-15'
AND '06-30'
THEN 47
WHEN TO_CHAR (dt, 'MM-DD') BETWEEN '08-01'
AND '09-30'
THEN 61
WHEN TO_CHAR (dt, 'MM-DD') BETWEEN '11-11'
AND '12-31'
THEN 51
ELSE 0
END
, 'Q'
) AS s
FROM q_sample
ORDER BY dt
;
where s is the normal start-of-quarter date (Jan. 1, Apr. 1, July 1 or Oct 1) in the same "fiscal quarter" as dt.
This gets the right results, but it seems thick and heavy, with that big CASE expression. Also, I don't like converting the dates to strings, or the magic numbers (59, 47 61 and 51). I'm wondering if there's a simpler and/or more efficient way to do this.
Here's some sample data I used for testing:
CREATE TABLE q_sample AS
SELECT DATE '2016-02-01' AS dt FROM dual UNION ALL
SELECT DATE '2016-02-02' FROM dual UNION ALL
SELECT DATE '2016-02-29' FROM dual UNION ALL
SELECT DATE '2016-05-14' FROM dual UNION ALL
SELECT DATE '2016-05-15' FROM dual UNION ALL
SELECT DATE '2016-07-31' FROM dual UNION ALL
SELECT DATE '2016-08-01' FROM dual UNION ALL
SELECT DATE '2016-09-28' FROM dual UNION ALL
SELECT DATE '2016-09-29' FROM dual UNION ALL
SELECT DATE '2016-11-10' FROM dual UNION ALL
SELECT DATE '2016-11-11' FROM dual UNION ALL
SELECT DATE '2016-12-24' FROM dual UNION ALL
SELECT DATE '2016-12-25' FROM dual UNION ALL
SELECT DATE '2016-12-31' FROM dual UNION ALL
SELECT DATE '2017-01-01' FROM dual UNION ALL
SELECT DATE '2017-02-01' FROM dual UNION ALL
SELECT DATE '2017-02-02' FROM dual
;
and here are the desired results (which the query above produces):
DT S
------------- -------------
01-Feb-2016 01-Jan-2016
02-Feb-2016 01-Apr-2016
29-Feb-2016 01-Apr-2016
14-May-2016 01-Apr-2016
15-May-2016 01-Jul-2016
31-Jul-2016 01-Jul-2016
01-Aug-2016 01-Oct-2016
28-Sep-2016 01-Oct-2016
29-Sep-2016 01-Oct-2016
10-Nov-2016 01-Oct-2016
11-Nov-2016 01-Jan-2017
24-Dec-2016 01-Jan-2017
25-Dec-2016 01-Jan-2017
31-Dec-2016 01-Jan-2017
01-Jan-2017 01-Jan-2017
01-Feb-2017 01-Jan-2017
02-Feb-2017 01-Apr-2017
I'd be open to solutions that mapped all the values in the fiscal quarter to any distinct value (not necessarily Jan 1., Apr 1, July 1 and Oct. 1 as above), even a non-DATE, if necessary.
If I had a real application that needed this, I think I'd create a calendar table that had the dates already mapped, and not worry about how slow and ugly the code that populated the table was.
This question is a tangent from a post earlier today: