How to get number of working days in a month in SQL

[chrisjones]

Hi Friends,
i need to get the number of workling days in a month using sql query..

so please help me by giving some sample code

[Krazykoolrohit]

Follow this topic being discussed here:

viewtopic.php?t=102642&highlight=number

[kcbland]

There is no sample code. Whether a day is a business day or not cannot be programmatically determined by anyone. You need a TIME dimension table, from that you can get your answer quite easily.

If you're going to assume that every Monday thru Friday is a working day, you can write something. However, not knowing your database we couldn't even give you an adequate answer. If you have Oracle, I'd suggest a user defined function. But I don't have one to give because I'd never do it this way, your answer is GUARANTEED to be wrong if you need to discount holidays, government holidays, optional holidays, etc.

[chrisjones]

Thanks for your reply,
I am assuming that every Monday thru Friday is a working day and sat and sun as holiday..
i want the SQL code,

There is no sample code. Whether a day is a business day or not cannot be programmatically determined by anyone. You need a TIME dimension table, from that you can get your answer quite easily.

If you're going to assume that every Monday thru Friday is a working day, you can write something. However, not knowing your database we couldn't even give you an adequate answer. If you have Oracle, I'd suggest a user defined function. But I don't have one to give because I'd never do it this way, your answer is GUARANTEED to be wrong if you need to discount holidays, government holidays, optional holidays, etc.

[kcbland]

DATABASE?

[hariprasad]

My Database is Oracle.

DATABASE? ...

[kumar_s]

You have WeekdayFromDate() available in Datastage. Returns the day numberof the week from the given date. You can use this to identify the whether the given date is week day or week end.

[kcbland]

My Database is Oracle.

DATABASE? ...

Fantastic, but it's not your question to answer. A person demanding a database specific query solution should have the courtesy to tell us the database they are using.

Else I'll be tempted to answer with SQL for the most arcane RDBMS I can think up.

[meena]

Hi Chrisjones....
I did a seacrh in google based on working_days function in oracle. I found a function to calculate number of working days.I am posting the function here.Write a SQL with part of this fucntion and use 'where' to pass the values of the date_one and date_two.

Well hope this may give you some idea...

Code: Select all

CREATE OR REPLACE FUNCTION "WORKING_DAYS_FN" (date_one IN DATE, date_two IN DATE) 
RETURN NUMBER 

IS 
working_days NUMBER; 
BEGIN 
CASE SIGN (date_one - date_two) 
WHEN -1 
THEN 
SELECT COUNT (date_calc) 
INTO working_days 
FROM calendar_table
WHERE date_calc <= date_two 
AND date_calc > date_one 
AND LOWER (TO_CHAR (date_calc, 'day')) NOT LIKE '%sun%' 
AND LOWER (TO_CHAR (date_calc, 'day')) NOT LIKE '%sat%' 
AND holiday IS NULL; 
ELSE 
SELECT COUNT (date_calc) 
INTO working_days 
FROM calendar_table 
WHERE date_calc <= date_one 
AND date_calc > date_two 
AND LOWER (TO_CHAR (date_calc, 'day')) NOT LIKE '%sun%' 
AND LOWER (TO_CHAR (date_calc, 'day')) NOT LIKE '%sat%' 
AND holiday IS NULL; 
END CASE; 

RETURN working_days; 
END;

[kcbland]

Great meena, you gave him a solution utilizing a table "calendar_table", which is just like a Time dimension....

[meena]

Hi Kenneth Bland..
Thank you..... :D

Great meena, you gave him a solution utilizing a table "calendar_table", which is just like a Time dimension.... ...

[clshore]

Tough to do in plain Sql without some kind of calendar table,
but assuming you DO have column 'date' in 'dateTable',
this will count the weekdays in a specified year and month:

select count(*) from dateTable
where to_char(date,'YYYYMM') = '200608' -- specify the year and month
and to_char(date,'D') between 2 and 6 -- get weekdays MO-FR

Carter

[prabu]

My Database is Oracle.

DATABASE? ...

Fantastic, but it's not your question to answer. A person demanding a database specific query solution should have the courtesy to tell us the database they are using.

Else I'll be tempted to answer with SQL for the most arcane RDBMS I can think up.

Code: Select all

select 
sum(
case 
 when to_char(trunc(last_day(sysdate))-level,'DY')='MON' then
 1
 when to_char(trunc(last_day(sysdate))-level,'DY')='TUE' then
 1
 when to_char(trunc(last_day(sysdate))-level,'DY')='WED' then
 1
 when to_char(trunc(last_day(sysdate))-level,'DY')='THU' then
 1
 when to_char(trunc(last_day(sysdate))-level,'DY')='FRI' then
 1
else
 0
end
) working_day,
sum(
case 
 when to_char(trunc(last_day(sysdate))-level,'DY')='SUN' then
 1
 when to_char(trunc(last_day(sysdate))-level,'DY')='SAT' then
 1
else
 0
end
) holiday,
count(level) total_days
 from dual
connect by level<to_char(trunc(last_day(sysdate)),'DD')+1
/

i cannot think of a more procedural way, thanks clshore
connect by level is a trick , BTW
hope somebody gets benefitted :D