using subquery how can i calculate working days in a month?

Question Posted / mrutunjay.samal

3 Answers
13162 Views
Spice Telecom, I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

using subquery how can i calculate working days in a month?..

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

using subquery how can i calculate working days in a month?..

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

using subquery how can i calculate working days in a month?..

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

Post New Answer