Page 1 of 1
How to get number of working days in a month in SQL
Posted: Tue Aug 08, 2006 10:12 am
by 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
Posted: Tue Aug 08, 2006 11:01 am
by Krazykoolrohit
Follow this topic being discussed here:
viewtopic.php?t=102642&highlight=number
Posted: Tue Aug 08, 2006 11:15 am
by 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.
How to get number of working days in a month in SQL
Posted: Tue Aug 08, 2006 12:56 pm
by 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,
kcbland wrote: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.
Posted: Tue Aug 08, 2006 1:49 pm
by kcbland
DATABASE?
How to get number of working days in a month in SQL
Posted: Tue Aug 08, 2006 7:43 pm
by hariprasad
My Database is Oracle.
kcbland wrote:DATABASE? ...
Posted: Tue Aug 08, 2006 7:53 pm
by 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.
Re: How to get number of working days in a month in SQL
Posted: Tue Aug 08, 2006 8:11 pm
by kcbland
hariprasad wrote:My Database is Oracle.
kcbland wrote: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.
Re: How to get number of working days in a month in SQL
Posted: Tue Aug 08, 2006 9:51 pm
by 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;
Posted: Wed Aug 09, 2006 6:53 am
by kcbland
Great meena, you gave him a solution utilizing a table "calendar_table", which is just like a Time dimension....
Posted: Wed Aug 09, 2006 7:41 am
by meena
Hi Kenneth Bland..
Thank you..... :D
kcbland wrote:Great meena, you gave him a solution utilizing a table "calendar_table", which is just like a Time dimension.... ...
Posted: Wed Aug 09, 2006 10:58 am
by 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
Re: How to get number of working days in a month in SQL
Posted: Wed Aug 09, 2006 11:02 am
by prabu
kcbland wrote:hariprasad wrote:My Database is Oracle.
kcbland wrote: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