Re: sql
Try
select whatever
from a_table
where weekday(a_date_column) not in ("6","0")
or somesuch
and a_date_column between "010196" and "012496"
0=Sunday
HTH
- John
: > 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
--
John Regep, || _______ || ZenaComp, Incorporated
Information Consultant || -----/ / || 17187 N.Laurel Park Dr. #351
jregep@zenacomp.com || / /______ || Livonia, Michigan 48152
1-(313)-464-3700 || -------- || FAX (313)-464-3730