Hi All,
I have to write a routine to find the number of working days within a given period excluding the weekends and the holidays.
My question is that can SQL queries be used in creating routines.If yes then what will be the syntax.
If anybody can help with an example, it would be of great help.
Thanks to All
SQL query in DS routines
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
Thanks for the reply.
But i'll have to check for each date whether its a weekday or not. For every chk, there will be a call to unix, then to sqlplus. I think it will not be a feasable option.
Can you suggest me how to write routine for calculating working days.
I am totally messed up what to do.
Thanks
But i'll have to check for each date whether its a weekday or not. For every chk, there will be a call to unix, then to sqlplus. I think it will not be a feasable option.
Can you suggest me how to write routine for calculating working days.
I am totally messed up what to do.
Thanks
I would not shell out to a database, but either write a routine in C++ and bind that to the job, or use a date dimension table.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Simplest will be to have a table in your database containing flags for holidays.
This way you can do a simple
If you want in routine, you need to
1.) find the next week-start after start date
2.) find the prev week-end before end date
3.) find working days in start week after (or from) start date
4.) find working days in end week before (or upto) end date
5.) do ( {2} - {1} ) * 5 / 7 + {3} + {4}
There are also other ways like
locating day of week which is same as start date in the "end date week" and then find difference of working days in the "end date week" compared to that date.
This way you can do a simple
Code: Select all
count(*) where flag is null
1.) find the next week-start after start date
2.) find the prev week-end before end date
3.) find working days in start week after (or from) start date
4.) find working days in end week before (or upto) end date
5.) do ( {2} - {1} ) * 5 / 7 + {3} + {4}
There are also other ways like
locating day of week which is same as start date in the "end date week" and then find difference of working days in the "end date week" compared to that date.