Solved

Oracle SQL - 5-4-4 Month ends

Posted on 2011-02-25
8
402 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

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.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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 shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 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

14 Experts available now in Live!

Get 1:1 Help Now