Re: sql

bayoff@izzy.net (bayoff) wrote: >I am trying to write a SQL statement that will look at 2 dates and determine >the number of work days there are between them. I need to issue the SQL >against many rows. If I can at least exclude the week-ends that would be >great. The holidays, a bonus.... Does anyone have an answer? We have done this - in 4gl. We created a file called /etc/holidays in which was listed the dates of holidays for the coming year. The format is one date per line, followed by a "|". We then load a temporary table with these dates. If the maximum date loaded is less than a month in the future, then a warning is displayed and mailed to the database administrator, warning them to update the holiday file. Then : if weekday(TODAY) != 0 # not a Sunday and weekday(TODAY) != 6 # not a Saturday and NOT holiday(TODAY) then # not a Holiday # then it is a working day Obviously, the holiday function attempts to find the passed date in the temporary holiday table. To achieve the same end using SQL, you could have a permanent table that holds non-working days. Obviously, holidays would have to be added and maintained by somebody, whereas Sat and Sun could be added automatically (Eg a cron job running every Sat or Sun which inserts (TODAY+7) into the table, to add the next Sat or Sun). HTH. Cheers, Brent Jackson. brent@hypercom.co.nz Phone : 64-9-3603593 Fax : 64-9-3602840