asked on

Oracle SQL - 5-4-4 Month ends

Hey there,

I'm looking for a single select to identify the month end based on a 5-4-4 pattern. I'm new to this pattern and I'm not not sure about "catching up" but it looks as though the month ends for Jun and Dec are the actual last days of the month (30 and 31). Here's the list I'm working with - it only has 2009 and 2010, but I would like the select to work with any date range.

Jan 09 - Jan 31st
Feb 09 - Feb 28
Mar 09 - Mar 28
Apr 09 - May 2
May 09 - May 30
Jun 09 - Jun 30
Jul 09 - Aug 1
Aug 09 - Aug 28
Sept 09 - Sept 26
Oct 09 - Oct 31
Nov 09 - Nov 28
Dec 09 - Dec 31

Jan 10 - Jan 30
Feb 10 - Feb 27
Mar 10 - Mar 27
Apr 10 - May 1
May 10 - May 28
Jun 10 - Jun 30
Jul 10 - Jul 31
Aug 10 - Aug 28
Sept 10 - Sept 25
Oct 10 - Oct 30
Nov 10 - Nov 27
Dec 10 - Dec 31

And a little more detail...
SELECT order, order_date, MONTH_END
FROM Orders

Basically, based on the "order_date" I'd like the MONTH_END to calulate in this column so I can reference it when nested.

I'm really trying to make this as uncomplicated as possible...

I've tried different NEXT_DAY(LAST_DAY()) combinations and can't get any to work. Any insight would be greatly appreciated...

One more thing, this has to be accomplished with a SELECT. I cannot create or use any stored procedures in this database.

Cheers!

Sean Stuber

How does Jan 31 or Jan 30 fit into a 5-4-4 pattern?

Can you describe your calendar rules a bit more?

Sharath S

You can get the first and last day of the month like this.

SELECT order, 
       order_date, 
       TRUNC(order_date,'MM') FirstDayOfMonth, 
       LAST_DAY(order_date)   LastDayOfMonth 
  FROM Orders

Open in new window

Are you looking for something else?

Sean Stuber

Are you using 4-4-5 instead? Assuming a first Sunday start, 4-4-5 for 2010 would end on Jan 30.

Sean Stuber

ooops, I meant to say First Saturday, not Sunday, but even so, I don't see a 4-4-5 or a 5-4-4 continuing for the other months.
Maybe I'm miscounting

NEVAEHSIN

ASKER

It looks like this....

June and December - month end is always the actual month end.

For the January question - 5-4-4: Last day of the 5 week in 2009 was Jan31, Last day of week 5 in 2010 was Jan 30.
The month ends (aside from June and December) always land on Saturdays.

May 28 - 2010 should be May 29 (that's the saturday)

I've put in the code I've been working on - but there HAS to be an easier way...

SELECT DISTINCT 
CASE 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') IN('05', '09', '13', '22') THEN 
		CASE 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') = 'SAT' THEN LAST_DAY(ord_date) 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') <> 'SAT' THEN NEXT_DAY(LAST_DAY(ord_date)-7, 'SAT')
		END 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') IN('18', '31') THEN 
		CASE 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') = 'SAT' THEN LAST_DAY(ord_date) 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') <> 'SAT' THEN NEXT_DAY(LAST_DAY(ord_date), 'SAT')
		END 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') IN('26', '52') THEN LAST_DAY(ord_date)/*
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '22' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '26' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '31' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '35' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '39' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '44' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '48' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '52' THEN */
END AS MEnd,
TO_CHAR(CASE 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') IN('05', '09', '13', '22') THEN 
		CASE 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') = 'SAT' THEN LAST_DAY(E_Ord_H.ord_date) 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') <> 'SAT' THEN NEXT_DAY(LAST_DAY(ord_date)-7, 'SAT')
		END 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') IN('18', '31') THEN 
		CASE 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') = 'SAT' THEN LAST_DAY(E_Ord_H.ord_date) 
			WHEN TO_CHAR(LAST_DAY(ord_date), 'dy') <> 'SAT' THEN NEXT_DAY(LAST_DAY(ord_date), 'SAT')
		END 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') IN('26', '52') THEN LAST_DAY(ord_date)/*
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '22' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '26' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '31' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '35' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '39' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '44' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '48' THEN 
	WHEN TO_CHAR(LAST_DAY(ord_date), 'ww') = '52' THEN */
END, 'ww') AS WEEK 
FROM Orders 
WHERE E_Ord_H.Comp_Code = 'W1' AND E_Ord_H.Cust_Code = 'CAM944' 
	AND E_Ord_H.ord_date BETWEEN TO_DATE('01.01.09', 'dd.mm.yy') AND TO_DATE('31.12.10', 'dd.mm.yy') 
ORDER BY MEnd

Open in new window

NEVAEHSIN

ASKER

FYI - obviously the where in the above code is out to lunch with regards to the select O.o and really shouldn't have been included :p

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NEVAEHSIN

ASKER

Works perfect! THANKS! (And I'm pretty sure you are right about the two incorrect dates as well!