SQL query in DS routines

[shalini11]

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

[algfr]

Not very sure but you can call unix command in your routine.

From there you can run a command using sqlplus and thus running a stored procedure or a SQL statement.

[priyadarshikunal]

Search and you might find a routine syntax posted by Arnd for running sql queries in routines.

[shalini11]

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

[ArndW]

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.

[Sainath.Srinivasan]

Simplest will be to have a table in your database containing flags for holidays.

This way you can do a simple

Code: Select all

count(*) where flag is null 

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.