Re: sql

Hi, This isn't exactly what you want, but it should point you in the right direction. It is an ACE report that I wrote in response to a request that our database should be able to calculate the response time for samples received by our labs, and automatically determine the percentage of samples dealt with in 3 working days. To get rid of weekends, you use the "weekday" function, that returns an integer eg 1 = Monday, 2 = Tuesday etc. On every row the report calculates the number of working days, and adds 1 to the value of y if it passes, and adds 1 to the value of x if it fails. On the last row, it calculates the percentage. Sorry I haven't got time to "tart it up" - maybe later. NB (1) THE DATES ARE IN UK DMY2/ FORMAT NB (2) BEFORE I'M CAUGHT BY THE DATABASE DESIGN POLICE, I WOULD LIKE TO SAY THAT THE DATABASE I AM CURRENTLY WORKING ON TO REPLACE THIS ONE USES 4 DIGIT YEARS :-) Hope this helps, Richard. ----------------------------------------------------------------- | _________ | Richard Thomas | | / /_______| | r.thomas@csl.gov.uk | | / /__/ | | | /_/ | TRIGGER happy ;-) | | | | ----------------------------------------------------------------- > > 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? > database xxx end define variable pstart date variable pend date variable x smallint variable y smallint variable z smallint end input prompt for pstart using "Enter date of start of period : " prompt for pend using "Enter date of end of period : " end output report to pipe "more" top margin 0 left margin 0 end select weekday(date1) rday, weekday(date2) dday, (date2 - date1) lagtime, date2 from samptab where date1 >= $pstart and date1 <= $pend end format on every row if lagtime > 6 then let x = x + 1 else if dday = 1 and (rday = 4 or rday = 5) then let y = y + 1 else if dday = 2 and rday = 5 then let y = y + 1 else if date2 = 03/01/95 and (rday = 4 or rday = 5) then let y = y + 1 else if date2 = 04/01/95 and rday = 5 then let y = y + 1 else if date2 = 18/04/95 and (rday = 2 or rday = 3) then let y = y + 1 else if date2 = 19/04/95 and rday = 3 then let y = y + 1 else if date2 = 09/05/95 and (rday = 4 or rday = 5) then let y = y + 1 else if date2 = 10/05/95 and rday = 5 then let y = y + 1 else if date2 = 31/05/95 and (rday = 4 or rday = 5) then let y = y + 1 else if date2 = 01/06/95 and rday = 5 then let y = y + 1 else if date2 = 28/08/95 and (rday = 4 or rday = 5) then let y = y + 1 else if date2 = 29/08/95 and rday = 5 then let y = y + 1 else if date2 = 28/12/95 and (rday = 4 or rday = 5) then let y = y + 1 else if date2 = 29/12/95 and rday = 5 then let y = y + 1 else if lagtime <= 2 then let y = y + 1 else let x = x + 1 on last row let z = x + y skip 1 line print "For the period ", pstart, " to ", pend, ", ", z using "<<<&", " samples were received." skip 1 line print "Of these, the following proportion received a final or ", "provisional diagnosis" print "within 3 days:" skip 1 line print column 32, "~b", (y/z)*100 using "##&", "%", "~c" skip 1 line if ((y/z)*100) >= 94.5 then print "This is within the target of 95% - well done" else print "This is below the target of 95%" end