I need the following access query converted to SQL Server view.  The
logic has to be included in the query, values working days, dayslate,
days0015, days1630, days3145, days4660 have to be expressed
numerically in number of days.  For example 10/5/02 - 9/4/02 = 31,
(not 1/2/00). Function 'workdays' in query can be substituted to make
this work.  Thanks in advance.

SELECT [Client List].[Client Name], [Job Names].[Job Name],
IssueTrack.*, [Team Members].Name, [Project Phase].[Phase Category],
IssueTrack.[Phase Code], WorkDays(Date(),[Date Due]) AS WorkingDays,
IIf(WorkDays(Date(),[Date Due])<0,True,False) AS DaysLate,
IIf(WorkDays(Date(),[Date Due])>=0 And WorkDays(Date(),[Date
Due])<16,True,False) AS Days0015, IIf(WorkDays(Date(),[Date Due])>=16
And WorkDays(Date(),[Date Due])<31,True,False) AS Days1630,
IIf(WorkDays(Date(),[Date Due])>=31 And WorkDays(Date(),[Date
Due])<46,True,False) AS Days3145, IIf(WorkDays(Date(),[Date Due])>=45
And WorkDays(Date(),[Date Due])<61,True,False) AS Days4660,
IIf(WorkDays(Date(),[Date Due])>=60,True,False) AS Days6000
FROM (((IssueTrack LEFT JOIN [Job Names] ON IssueTrack.[Job Number] =
[Job Names].[Job Number]) LEFT JOIN [Client List] ON [Job
Names].[Client Family Number] = [Client List].[Client Family Number])
LEFT JOIN [Project Phase] ON IssueTrack.[Phase Code] = [Project
Phase].[Phase Code]) LEFT JOIN [Team Members] ON IssueTrack.RP11 =
[Team Members].TeamMemberID

---

Clarification of Question by sailor94708-ga on 04 Oct 2002 14:16 PDT

Layne,

I just tried this, I'm getting this error, "The Query Designer does
not support the CASE SQL construct.", you have the concept down, if
you determine another approach - thanks -btw using sql server 2000
thanks again.

I haven't checked this code yet (I don't have access to a SQL Server
where I am right now), but this should be pretty close to what you
want barring any syntax errors:

The bulk of the work is figuring out working days.  Here's my approach
(may be better ways).  The number of calendar days / 7 is the number
of weeks (fractional) and then * 5 is the number of working days. 
Next I take into account if the starting or ending dates are weekends
(just in case) and subtract out one.  Since this is project data, I
add one back in in case the start and end date are same (it isn't late
until TOMORROW).

So, for the date range 9/1/2002 to 9/30/2002, the number of calendar
days is 30.  30 / 7.0 * 5 = 21 working days.  Since 9/1/2002 is a
Sunday, we subtract 1 day from this total to give 20 days.  And we add
1 back in to count the 30th as a working day.  (I don't remember
whether SQL Server will return 29 or 30 for the DATEDIFF function, so
you may need to add one to make it all work out.)

select	Client_List.client_name,
	Job_Names.job_name,
	issuetrack.*,
	Team_Memebers.name,
	Project_Phase.phase_category,
	issuetrack.phase_code,
	WorkingDays = floor( datediff( 'd', due_date, getdate() ) / 7.0 * 5.0
) - ( case when datepart( getdate(), 'dw' ) = 1 then 1 else 0 end ) -
( case when datepart( getdate(), 'dw' ) = 7 then 2 else 0 end ) - (
case when datepart( due_date, 'dw' ) = 1 then 2 else 0 end ) - ( case
when datepart( due_date, 'dw' ) = 7 then 1 else 0 end ) + 1,
	DaysLate = case sign( floor( datediff( 'd', due_date, getdate() ) /
7.0 * 5.0 ) - ( case when datepart( getdate(), 'dw' ) = 1 then 1 else
0 end ) - ( case when datepart( getdate(), 'dw' ) = 7 then 2 else 0
end ) - ( case when datepart( due_date, 'dw' ) = 1 then 2 else 0 end )
- ( case when datepart( due_date, 'dw' ) = 7 then 1 else 0 end ) + 1 )
when -1 then TRUE else FALSE end,
	Days0015 = case when( floor( datediff( 'd', due_date, getdate() ) /
7.0 * 5.0 ) - ( case when datepart( getdate(), 'dw' ) = 1 then 1 else
0 end ) - ( case when datepart( getdate(), 'dw' ) = 7 then 2 else 0
end ) - ( case when datepart( due_date, 'dw' ) = 1 then 2 else 0 end )
- ( case when datepart( due_date, 'dw' ) = 7 then 1 else 0 end ) + 1
between 0 and -15 ) then TRUE else FALSE end,
	Days1630 = case when( floor( datediff( 'd', due_date, getdate() ) /
7.0 * 5.0 ) - ( case when datepart( getdate(), 'dw' ) = 1 then 1 else
0 end ) - ( case when datepart( getdate(), 'dw' ) = 7 then 2 else 0
end ) - ( case when datepart( due_date, 'dw' ) = 1 then 2 else 0 end )
- ( case when datepart( due_date, 'dw' ) = 7 then 1 else 0 end ) + 1
between -16 and -30 ) then TRUE else FALSE end,
	Days3145 = case when( floor( datediff( 'd', due_date, getdate() ) /
7.0 * 5.0 ) - ( case when datepart( getdate(), 'dw' ) = 1 then 1 else
0 end ) - ( case when datepart( getdate(), 'dw' ) = 7 then 2 else 0
end ) - ( case when datepart( due_date, 'dw' ) = 1 then 2 else 0 end )
- ( case when datepart( due_date, 'dw' ) = 7 then 1 else 0 end ) + 1
between -31 and -45 ) then TRUE else FALSE end,
	Days4660 = case when( floor( datediff( 'd', due_date, getdate() ) /
7.0 * 5.0 ) - ( case when datepart( getdate(), 'dw' ) = 1 then 1 else
0 end ) - ( case when datepart( getdate(), 'dw' ) = 7 then 2 else 0
end ) - ( case when datepart( due_date, 'dw' ) = 1 then 2 else 0 end )
- ( case when datepart( due_date, 'dw' ) = 7 then 1 else 0 end ) + 1
between -46 and -60 ) then TRUE else FALSE end,
	Days6000 = case when( floor( datediff( 'd', due_date, getdate() ) /
7.0 * 5.0 ) - ( case when datepart( getdate(), 'dw' ) = 1 then 1 else
0 end ) - ( case when datepart( getdate(), 'dw' ) = 7 then 2 else 0
end ) - ( case when datepart( due_date, 'dw' ) = 1 then 2 else 0 end )
- ( case when datepart( due_date, 'dw' ) = 7 then 1 else 0 end ) + 1 <
-60 ) then TRUE else FALSE end,
FROM (((IssueTrack LEFT JOIN [Job Names] ON IssueTrack.[Job Number] =
[Job Names].[Job Number]) LEFT JOIN [Client List] ON [Job 
Names].[Client Family Number] = [Client List].[Client Family Number])
LEFT JOIN [Project Phase] ON IssueTrack.[Phase Code] = [Project 
Phase].[Phase Code]) LEFT JOIN [Team Members] ON IssueTrack.RP11 = 
[Team Members].TeamMemberID



Layne