|
View previous topic :: View next topic
|
| Author |
Message |
padmavathi
New User
Joined: 25 Mar 2008
Posts: 6
Location: chennai
|
|
|
|
Hi
I need a query to calculate the 3rd working day for a month excluding the saturdays and sundays for a month..
can any one help me for executing this. |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
Do You have an idea of the algorithm to be used ??
I am asking in order to understand how much detail You need in the explanation
by the way it' s rather complicated to implement as a SQL/db2 query
also what if an holiday falls in the middle ...
may first, labor day is an holiday for example |
|
| Back to top |
|
|
padmavathi
New User
Joined: 25 Mar 2008
Posts: 6
Location: chennai
|
|
|
|
Hi
a change in my question.I need a query to calculate the 3rd last working day for a month excluding the saturdays and sundays for a month..
can any one help me for executing this.[/quote] |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
it does not make any difference between the fisrt or the last...
can You reply to my previous post please
what if it cannot be done with a query,
but You need to write a program/subroutine/or an udf ??? |
|
| Back to top |
|
|
padmavathi
New User
Joined: 25 Mar 2008
Posts: 6
Location: chennai
|
|
|
|
| No necessity of considering the holidays other than saturdays and sundays..i need only 3 days before last working day. i will have the end date of that month. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
Try this
| Code: |
CREATE FUNCTION TARGETDATE (MDATE DATE)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURNS DATE
RETURN
LAST_DAY(MDATE) -
(CASE DAYOFWEEK(LAST_DAY(MDATE))
WHEN 1 THEN 5
WHEN 2 THEN 5
WHEN 3 THEN 5
WHEN 4 THEN 5
WHEN 5 THEN 3
WHEN 6 THEN 3
WHEN 7 THEN 4 END) DAYS;
|
| Code: |
WS-DATE CAN BE ANY DATE IN THE MONTH.
SELECT TARGETDATE(DATE(:WS-DATE)) INTO :WS-TARGET FROM SYSIBM.SYSDUMMY1
OR SET :WS-TARGET = TARGETDATE(DATE(:WS-DATE))
WS-DATE = '2008-01-01' WS-TARGET = '2008-01-28'
WS-DATE = '2008-02-01' WS-TARGET = '2008-02-26'
WS-DATE = '2008-03-01' WS-TARGET = '2008-03-26'
WS-DATE = '2008-04-01' WS-TARGET = '2008-04-25'
WS-DATE = '2008-05-01' WS-TARGET = '2008-05-27'
WS-DATE = '2008-06-01' WS-TARGET = '2008-06-25'
WS-DATE = '2008-07-01' WS-TARGET = '2008-07-28'
WS-DATE = '2008-08-01' WS-TARGET = '2008-08-26'
WS-DATE = '2008-09-01' WS-TARGET = '2008-09-25'
WS-DATE = '2008-10-01' WS-TARGET = '2008-10-28'
WS-DATE = '2008-11-01' WS-TARGET = '2008-11-25'
WS-DATE = '2008-12-01' WS-TARGET = '2008-12-26'
|
|
|
| Back to top |
|
|
padmavathi
New User
Joined: 25 Mar 2008
Posts: 6
Location: chennai
|
|
|
|
hi
thank you..
its working fine  |
|
| Back to top |
|
|
|
|
