Here's a complicated, but effective method:
Note: I am assuming the first week of the year includes the first day
of the year.
Table: Porder
Columns: JobNo
DateEnt
SELECT Job3 DateEnt (((DateEnt - IDWK(DateEnt) + 8) -
(RDATE(01,01,IYR(DateEnt)) - IDWK(RDATE(01,01,IYR(DateEnt))) + 1))/7)
FROM Porder
Breaking the formula down:
(DateEnt - IDWK(DateEnt) + 8) moves the date the job was entered to the
next Monday.
(RDATE(01,01,IYR(DateEnt)) - IDWK(RDATE(01,01,IYR(DateEnt))) + 1) finds
the date Monday of the week containing the first day of the year.
Subtract the two to get the total number of days between the dates and
divid by 7 to get the total number of weeks.
You can either use this in the select statement or create a computed
column with this as the expression.
Hope this helps
Bill
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
On Behalf Of David Ebert
Sent: Thursday, October 11, 2001 1:43 PM
To: [EMAIL PROTECTED]
Subject: need an IWK
I've been asked to produce a report on an ongoing basis that reports, by
week for a given month, certain productivity stats.
The weekly part has me stumped. The best I can think of is an
annually-updated 52 row table, but the "annually-updated" is a flop
waiting
to happen.
Can I say logically that a given date falls in week (n) in any given
year?
tia
Dave
