First working day of the month 5

[northw]

Hi All,
Can this query be further shrinked/optimized?
I am getting here the first working day of the month.

xxwfs_get_work_day is a function based on a holiday table which returns "1" for a federal holiday, "2" for saturday and sunday and "0" for a working day.

SELECT (CASE
          WHEN xxwfs_get_work_day 
                (CASE
                     WHEN (TO_CHAR (TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON''), ''DY'')) = ''SUN''
                     THEN TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''),''MON'')+1
                     WHEN (TO_CHAR (TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON''), ''DY'')) = ''SAT''
                     THEN TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')+2
                     ELSE TRUNC (TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')
                  END, ''USA'') = 1
          THEN
                CASE
                     WHEN (TO_CHAR (TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON''), ''DY'')) = ''SUN''
                     THEN TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')+1
                     WHEN (TO_CHAR (TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON''), ''DY'')) = ''SAT''
                     THEN TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')+2
                     ELSE TRUNC (TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')
                  END+1      
          ELSE CASE
                     WHEN (TO_CHAR (TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON''), ''DY'')) = ''SUN''
                     THEN TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')+1
                     WHEN (TO_CHAR (TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON''), ''DY'')) = ''SAT''
                     THEN TRUNC(TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')+2
                     ELSE TRUNC (TO_DATE ('''||as_of_dt||''', ''DD-MM-YY HH24:MI:SS''), ''MON'')
                  END
       END) T
  FROM DUAL

Thanks in advance!

 

[carp]

How about something like:

CREATE OR REPLACE FUNCTION first_work_day(as_of_dt IN DATE) RETURN DATE IS 
   l_date DATE := trunc(as_of_dt,'MONTH');
BEGIN
   WHILE to_char(l_date,'fmDay') IN ('Saturday','Sunday') LOOP
      l_date := l_date + 1;
   END LOOP;
   RETURN l_date;
END first_work_day;

 

[ChrisHunt]

So your SQL determines the first day of the month, adds 2 days if a saturday or 1 if a sunday, calls xxwfs_get_work_day() to see if the resulting day is a holiday and adds a another day if it is? What happens if 1st January falls on a Friday - it'll make Saturday 2nd January the first working day.

The simplest approach is to write a function along the lines suggested by carp, though I'd change it to use your holiday function:

CREATE OR REPLACE FUNCTION first_work_day(as_of_dt IN DATE) RETURN DATE IS 
   l_date DATE := trunc(as_of_dt,'MONTH');
BEGIN
   WHILE [red]xxwfs_get_work_day(l_date) > 0[/red] LOOP
      l_date := l_date + 1;
   END LOOP;
   RETURN l_date;
END first_work_day;

If you have to do it as a SQL statement, here's one way:

SELECT MIN(poss_date) first_date
FROM  (SELECT TRUNC(as_of_dt,'MM') + LEVEL -1 poss_date
       FROM   dual
       CONNECT BY LEVEL <= 4) x
WHERE  xxwfs_get_work_day(poss_date) = 0

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[northw]

Thanks Chris and Carp!

SQL statement solution worked perfectly, and gave me exactly what I wanted.

Any suggestion for the same thing for a week, date before last five business days, if it is holiday/saturdat/Sunday, then get the day before it.

I wrote this, but want to know if it can be optimized further?

select  (CASE
                  WHEN (TO_CHAR ((CASE WHEN xxwfs_get_work_day ((TO_DATE (as_of_dt, 'DD/MM/YYYY')-7), 'USA') = 1
                               THEN (TO_DATE (as_of_dt, 'DD/MM/YYYY')-7) - 1
                               ELSE (TO_DATE (as_of_dt, 'DD/MM/YYYY')-7) END), 'DY')) = 'SUN'
                  THEN (TO_DATE (as_of_dt, 'DD/MM/YYYY')-7) - 3
                  WHEN (TO_CHAR((CASE WHEN xxwfs_get_work_day ((TO_DATE (as_of_dt, 'DD/MM/YYYY')-7), 'USA') = 1
                               THEN (TO_DATE (as_of_dt, 'DD/MM/YYYY')-7) - 1 ELSE (TO_DATE (as_of_dt, 'DD/MM/YYYY')-7) END), 'DY')) = 'SAT'
                  THEN (TO_DATE (as_of_dt, 'DD/MM/YYYY')-7) - 2
                  ELSE (TO_DATE (as_of_dt, 'DD/MM/YYYY')-7)
                 END)
from dual;

Thanks again!

 

[carp]

OK, so I completely blew by your function - I need to slow down and read things more closely. Thanks for bringing us back on track, Chris!

If you are trying to optimize, then I would suggest you use the non-SQL query Chris provided for you. By avoiding the call to the database, it will be considerably more efficient. As for your new question, I would use exactly the same approach:

CREATE OR REPLACE FUNCTION week_ago_work_day(as_of_dt IN DATE) RETURN DATE IS 
   l_date DATE := as_of_dt - 7;
BEGIN
   WHILE xxwfs_get_work_day(l_date,'USA') > 0 LOOP
      l_date := l_date - 1;
   END LOOP;
   RETURN l_date;
END week_ago_work_day;