Solved

Oracle SQL - 5-4-4 Month ends

Posted on 2011-02-25
8
403 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
  • 4
  • 3
8 Comments
 
LVL 73

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 40

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 73

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
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now