I'm trying to target a date from the previous month that matches today. However, the target date needs to be based on the same number of business days left in the month. For example, if today is 6-JUN-14 then I need a query that gives me 7-MAY-14 since both dates have 16 business days left in their respective month. Also, I'm using SQL Developer.
Asked
Active
Viewed 114 times
2
-
1Can you give an example of what you've tried? – pstenstrm Jun 07 '14 at 18:22
-
Check this out first:http://stackoverflow.com/questions/252519/count-work-days-between-two-dates – Jose Cherian Jun 08 '14 at 00:53
-
@manu - Date arithmetic is something which varies considerably between the various flavours of RDBMS. So I don't think a link to SQL Server solutions is particularly helpful here. – APC Jun 08 '14 at 12:32
-
@APC, I still don't get why the logic can't be implemented in oracle SQL. Oracle supports datediff and datename functions, doesn't it? Sorry, not so familiar with Oracle. – Jose Cherian Jun 08 '14 at 14:55
-
@manu - no Oracle does not support `datediff` or `datename` functions. It implements those operations in a rather different fashion. – APC Jun 08 '14 at 20:11
1 Answers
1
There are a couple of ways to tell whether a day is weekday or weekend, but they are governed by your NLS settings. I'm in the UK, so my setting for NLS_TERRITORY determines that Saturday and Sunday are day number 6 and 7. However, other territories (such as USA) have Sunday as day number 1. Find out more.
Anyway, this query generates the weekdays between now and the end of June:
select weekday
from ( select weekday
, to_char(weekday,'D') as dayn
from ( select (sysdate + level) as weekday
from dual
connect by level <= (last_day(sysdate) - sysdate)
)
)
where dayn not in ('6','7')
/
... which gives this result set ...
8* where dayn not in ('6','7')
WEEKDAY
---------
09-JUN-14
10-JUN-14
...
26-JUN-14
27-JUN-14
30-JUN-14
16 rows selected.
SQL>
So we can wrap that logic in a sub-query to get the number of days, and use some LAST_DAY(), ADD_MONTHS() and TRUNC() magic to generate a similar list for the previous month.
with curr as ( select count(*) as nod
From
(
select weekday
from ( select weekday, to_char(weekday,'D') as dayn
from ( select sysdate + level weekday
from dual
connect by level <= (last_day(sysdate) - sysdate)
)
)
where dayn not in ('6','7')
)
)
, bds as ( select trunc(add_months(sysdate, -1), 'MM') fd
, last_day(add_months(sysdate, -1)) ld
from dual )
, prev as ( select weekday
, row_number() over (order by weekday desc) rn
from (
select fd + (level-1) weekday
from bds
connect by level <= ld-fd
)
where to_char(weekday,'D') not in ('6','7')
)
select prev.weekday
, rn
from prev
where prev.rn <= ( select nod+1 from curr )
order by prev.weekday
/
... generating the following range of dates in May ...
30* order by prev.weekday
WEEKDAY RN
--------- ----------
08-MAY-14 17
09-MAY-14 16
12-MAY-14 15
...
28-MAY-14 3
29-MAY-14 2
30-MAY-14 1
17 rows selected.
SQL>
Now, this range doesn't match the range you propose. That's because of Whitsun. In the UK 26-MAY-2014 was a bank holiday. However, there is nothing the Oracle date features can do to handle such things. So, if you want to include public holidays in the calculation you will need to create a reference table for them. We could include that table in the sub-query like this:
, prev as ( select weekday
, row_number() over (order by weekday desc) rn
from (
select fd + (level-1) weekday
from bds
connect by level <= ld-fd
)
where to_char(weekday,'D') not in ('6','7')
and weekday not in ( select holiday from public_holidays )
)
... which gives us this result set ...
WEEKDAY RN
--------- ----------
07-MAY-14 17
08-MAY-14 16
...
28-MAY-14 3
29-MAY-14 2
30-MAY-14 1
17 rows selected.
SQL>
APC
- 144,005
- 19
- 170
- 281