I need help writing a SQL statement?

Question

Our database records the weekending (WE) and the day (WORKDAY) our employee (EMPNO) was at the job (JOBNO). Unfortunately, our company has added a twist and starts thier workweek on Wednesdays.

I need a SQL formula that will convert WORKDAY and WE into a date. In Excel "=IF(WORKDAY<3, (WE-2+WORKDAY), (WE-9+WORKDAY))" works.

Also, I need the statement to find the MIN and MAX values in those dates to tell me when we started and finished the job (JOBNO). Any help would be appreciated. I've only been thinking about it for 90 minutes, and my head already hurts....

WE is a date. Always on Tuesday. The last WE is 07/04/2006. Thanks for all the help so far.

Thanks for all the help guys - this code seems to be working - not only does it find the first day we dtarted, but if we haven't started, it puts in the date we estimate starting.

SELECT

(CASE WHEN min(CASE when source = 'PR' then (dateadd(dd,(CASE when (0 + workday) > 2 then (0 + workday - 9) else (0 + workday - 2) end), we)) else NULL end) is null THEN eststart ELSE min(case when source = 'PR' then (dateadd(dd, (case when (0 + workday) > 2 then (0 + workday - 9) else (0 + workday - 2) end), we)) else '9999-12-31' end) END) as "startdate",

Answer 1

Check out SQL World on Planet Source Code:

http://www.pscode.com/vb/default.asp?lngWId=5

It should give you some ideas

Answer 2

For the first one you can actually set the day when the week starts. But it would be also possible using DayOfWeek Function
The second one is easy, Just say
Select WORKDAY from

Answer 3

what sort of data is in the fields now that could be matched to a date? If WE and WORKDAY are both integers it will be difficult at best since there is no way to map it to a month/year.

if you can map WE to a date, you should be able to do something along the lines of "WE - (5-workday) days"

Answer 4

try htmlgoodies.com. You will find lots of help there