Re: sql

From: Matt Marrow <marrowm_at_logica.com>
Date: 1996/01/24
Message-ID: <4e5v9f$90n_at_romeo.logica.co.uk>#1/1


In article <bayoff.4.0014AD7A_at_izzy.net>, bayoff_at_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?

Here's one idea - I am getting paid to do something else, so check it works yourself.....

For two columns, 'datea' and 'dateb' in a table 'testdates', one SQL statement (there are sure to be others, probably more elegant!) to find the working days between them is roughly:

SELECT 	(NEXT_DAY(datea,'SATURDAY') - datea) 
	+
	(5 - (NEXT_DAY(dateb,'SATURDAY') - dateb)) 
	+
	((((NEXT_DAY(dateb,'MONDAY')-7) - (NEXT_DAY(datea,'MONDAY'))))* 5 / 7)
FROM 	testdates

i.e.  	(number of days between datea and Saturday) + 
	(number of days between Monday and dateb) + 
	(5/7 * number of days between Saturday after datea and Monday before 
	dateb)

It almost certainly doesn't work for datea or dateb on Saturday or Sunday.....but it's a start!

As for bank holidays, we use a table called 'bank_holidays' with a single column 'holiday_day' which contains a list of all dates on which a bank holiday falls. Then you could also add a clause as follows:

<SQL stuff before> - (SELECT COUNT(*) FROM bank_holidays WHERE holiday_day BETWEEN datea AND dateb)

Is this any help?

Matt Marrow
(marrowm_at_logica.com)

Views are my own and do not necessarily reflect those of my employers Received on Wed Jan 24 1996 - 00:00:00 CET

Original text of this message