Solved

Oracle SQL - 5-4-4 Month ends

Posted on 2011-02-25
8
408 Views
Last Modified: 2012-05-11
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!
0
Comment
Question by:NEVAEHSIN
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 34983912
How does Jan 31 or Jan 30 fit into a 5-4-4 pattern?

Can you describe your calendar rules a bit more?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 34983917
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?
0
 
LVL 74

Expert Comment

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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 34983958
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
0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 34983965
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

0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 34984005
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
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34997500
By my math  I get

Aug 09 - Aug 29
and
May 10 - May 29

are you sure the 28th is correct for both of those months?


Here's the query I used
CASE
           WHEN TO_CHAR(ord_date, 'mm') IN ('01', '04', '07', '10')
           THEN
               NEXT_DAY(TRUNC(ord_date, 'yyyy') - 7, 'Sat')
               + 7 * (((TO_NUMBER(TO_CHAR(ord_date, 'q')) - 1) * 13) + 5)
           WHEN TO_CHAR(ord_date, 'mm') IN ('02', '05', '08', '11')
           THEN
               NEXT_DAY(TRUNC(ord_date, 'yyyy') - 7, 'Sat')
               + 7 * (((TO_NUMBER(TO_CHAR(ord_date, 'q')) - 1) * 13) + 9)
           WHEN TO_CHAR(ord_date, 'mm') IN ('03', '09')
           THEN
               NEXT_DAY(TRUNC(ord_date, 'yyyy') - 7, 'Sat')
               + 7 * TO_NUMBER(TO_CHAR(ord_date, 'q')) * 13
           WHEN TO_CHAR(ord_date, 'mm') IN ('06', '12')
           THEN
               LAST_DAY(ord_date)
       END month_end

Open in new window

0
 
LVL 1

Author Comment

by:NEVAEHSIN
ID: 34998999
Works perfect! THANKS!  (And I'm pretty sure you are right about the two incorrect dates as well!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question