using subquery how can i calculate working days in a month?
Answers were Sorted based on User's Feedback
Answer / 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;
/
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / mayru
SELECT COUNT( *)
FROM
(SELECT ROWNUM RNUM
FROM ALL_OBJECTS
WHERE ROWNUM <= TO_DATE('01-OCT-2011') - TO_DATE('01-SEP-2011') + 1
)
WHERE TO_CHAR(TO_DATE('01-SEP-2011') + RNUM - 1, 'DY') NOT IN('SAT', 'SUN');
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / lekurwale
U can caculate By creating the following function in SQL
server :
CREATE FUNCTION dbo.GetWorkingDays
( @StartDate datetime,
@EndDate datetime )
RETURNS INT
AS
BEGIN
DECLARE @WorkDays int, @FirstPart int
DECLARE @FirstNum int, @TotalDays int
DECLARE @LastNum int, @LastPart int
IF (DATEDIFF(day, @StartDate, @EndDate) < 2)
BEGIN
RETURN ( 0 )
END
SELECT
@TotalDays = DATEDIFF(day, @StartDate, @EndDate) - 1,
@FirstPart = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 6
WHEN 'Monday' THEN 5
WHEN 'Tuesday' THEN 4
WHEN 'Wednesday' THEN 3
WHEN 'Thursday' THEN 2
WHEN 'Friday' THEN 1
WHEN 'Saturday' THEN 0
END,
@FirstNum = CASE DATENAME(weekday, @StartDate)
WHEN 'Sunday' THEN 5
WHEN 'Monday' THEN 4
WHEN 'Tuesday' THEN 3
WHEN 'Wednesday' THEN 2
WHEN 'Thursday' THEN 1
WHEN 'Friday' THEN 0
WHEN 'Saturday' THEN 0
END
IF (@TotalDays < @FirstPart)
BEGIN
SELECT @WorkDays = @TotalDays
END
ELSE
BEGIN
SELECT @WorkDays = (@TotalDays - @FirstPart) / 7
SELECT @LastPart = (@TotalDays - @FirstPart) % 7
SELECT @LastNum = CASE
WHEN (@LastPart < 7) AND (@LastPart > 0) THEN
@LastPart - 1
ELSE 0
END
SELECT @WorkDays = @WorkDays * 5 + @FirstNum +
@LastNum
END
RETURN ( @WorkDays )
END
Is This Answer Correct ? | 6 Yes | 7 No |
Which is faster union or join?
How can we avoid duplicating records in a query?
how to create object in plsql
What is difference between stored procedure and trigger?
Difference between SUBSTR and INSTR?
Difference between DBMS and RDBMS...CODD's rules
How to execute multiple sql statements in a single annonymous block irrespective of the sql statement result?
What is the difference between stored procedure and view?
What is lookup table in sql?
How does cross join work?
How you will create Toad Function?
Which join is like inner join?