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;
/

What are the different types of a subquery?

1102

---

what is column? : Sql dba

1049

---

What is the main reason behind using an index?

1057

---

What is optimistic concurrency control? : Transact sql

1029

---

ERROR:Insert or update on table"accnt" violates foreign key constraints "acct_to_curr_symbol" DETAILS:KEY(accnt_curr_id)(-2)is not present in the table "curr_symbol" ......solve The Problem..

2552

---

What is attribute indicator in pl sql?

1065

---

What is cte sql?

1052

---

Is left join faster than inner join?

1168

---

What are the different schemas objects that can be created using pl/sql?

1082

---

What is the command used to fetch the first 5 characters of a string?

1257

---

What is the order of sql select?

1088

---

What version is sql?

1076

---

what is a unique key ? : Sql dba

1143

---

Explain the significance of the & and && operators in pl sql.

1046

---

what are date and time intervals? : Sql dba

1102

---