Answer Posted / lokesh

-- Table which maintains the Holidays list.
CREATE TABLE HOLIDAYS_TABLE
(
HOLIDAY_NAME VARCHAR2(20),
HOLIDAY_DATE DATE
);


-- procedure to Calculate the Number of Working days in a
period
CREATE OR REPLACE FUNCTION CALC_WORKING_DAYS(START_DATE IN
DATE, END_DATE IN DATE)

RETURN NUMBER

IS

v_stdate DATE := TO_DATE(START_DATE);
v_eddate DATE := TO_DATE(END_DATE);

v_k NUMBER := 0;
v_cnt NUMBER := 0;
v_bk_hldys NUMBER := 0;


BEGIN


-- To Find the total number of days in the period
v_k := v_eddate - v_stdate ;

-- To Exclude Weekends from the total number of days in
the period
FOR i IN 1..v_k LOOP

IF to_char(v_stdate,'Dy') NOT IN ('Sat','Sun') THEN
v_cnt := v_cnt +1;
END IF;
v_stdate := v_stdate + 1;

END LOOP;

-- To find the Public Holidays which are not on Weekends in
the given Period
SELECT COUNT(DISTINCT(HOLIDAY_DATE)) INTO v_bk_hldys FROM
HOLIDAYS_TABLE where HOLIDAY_DATE BETWEEN TO_DATE
(START_DATE) AND TO_DATE(END_DATE) AND to_char
(HOLIDAY_DATE,'Dy') NOT IN ('Sat','Sun');

-- To Subtract the public holidays which are not on
Weekends
v_cnt := v_cnt - v_bk_hldys;

RETURN v_cnt;
END;
/

describe transaction-safe table types in mysql : sql dba

944

---

what is a database transaction? : Sql dba

1106

---

Why are aggregate functions called so?

896

---

List out the acid properties and explain?

904

---

Is ms sql is free?

955

---

What is db journal file?

956

---

What does trigger mean in psychology?

913

---

What is sql*loader?

1073

---

what is a join? : Sql dba

961

---

Which join is default?

897

---

What is a table?

956

---

What is the difference between truncate and drop statements?

1084

---

Why cannot I use bind variables in ddl/scl statements in dynamic sql?

1133

---

What is difference between mysql and postgresql?

1043

---

What is a call statement? Explain with an example.

951

---