From Jet to SQL 2000 2

[jadams0173]

SQL 2000

I'm having a hard time converting these JET function to SQL. I've made several attempts but seem like a dig a deeper hole. I've successfully convert a few more of my JET queries to sql but this one is giving me a hard time.

First the JET query

SELECT J.JobNumber, J.PreReq, T.CourseNo, T.EmpNo, Max(T.CourseDate) AS TakenOn,
 tblSubCourse.SubCourseName, tblSubCourse.ExpirationInMonths, 
[red]Max(CDate(Format(IIf(IsNull([T].[EmpNo]),#1/1/1900#,IIf([ExpirationInMonths]=0,#12/31/2099#,
DateAdd("m",cint(([expirationInMonths])),[coursedate]))),"mm/dd/yyyy"))) AS ExpireDate, [/red]
IIf(isnull([Disqualified]),0,[t].[Disqualified])AS Disqual 
FROM (tblJobReq AS J LEFT JOIN [SELECT * FROM tblTrainingRecords WHERE EmpNo='06388']. AS T ON J.PreReq = T.CourseNo) LEFT JOIN tblSubCourse ON J.PreReq = tblSubCourse.SubCourseNumber WHERE (((J.JobNumber)= '101')) GROUP BY J.JobNumber, J.PreReq, T.CourseNo, T.EmpNo, tblSubCourse.SubCourseName, tblSubCourse.ExpirationInMonths, IIf(isnull([Disqualified]),0,[t].[Disqualified])

Now my latest unsuccesful attempt at SQL. I'm stuck on the part in red above.

[COLOR=blue]SELECT[/color] J.JobNumber, J.PreReq, [COLOR=blue]T[/color].CourseNo, [COLOR=blue]T[/color].EmpNo, [COLOR=#FF00FF]Max[/color]([COLOR=blue]T[/color].CourseDate) [COLOR=blue]AS[/color] TakenOn, 
tblSubCourse.SubCourseName, tblSubCourse.ExpirationInMonths, 

[COLOR=blue]IF[/color] [[COLOR=blue]T[/color]].[EmpNo] [COLOR=blue]IS[/color] NULL 
	[COLOR=red]'1/1/1900'[/color]
[COLOR=blue]Else[/color]
	[COLOR=blue]Begin[/color]
	

 	[COLOR=blue]Case[/color] [Tblsubcourse].[ExpirationInMonths] 
		[COLOR=blue]WHEN[/color] 0 [COLOR=blue]THEN[/color] [COLOR=#FF00FF]CONVERT[/color]([COLOR=#FF00FF]DATETIME[/color],[COLOR=red]'12/31/2099'[/color])
		[COLOR=blue]ELSE[/color] [COLOR=#FF00FF]DATEADD[/color] ( M , [Tblsubcourse].[ExpirationInMonths],[COLOR=#FF00FF]Max[/color](([[COLOR=blue]T[/color]].[CourseDate]))
	[COLOR=blue]END[/color])  [COLOR=blue]AS[/color] [ExpireDate] , 
[COLOR=blue]End[/color]
IIf([COLOR=#FF00FF]isnull[/color]([Disqualified]),0,[[COLOR=blue]t[/color]].[Disqualified])[COLOR=blue]AS[/color] Disqual
[COLOR=blue]FROM[/color] (tblJobReq [COLOR=blue]AS[/color] J [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] tblTrainingRecords [COLOR=blue]WHERE[/color] EmpNo=[COLOR=red]'06388'[/color]) [COLOR=blue]AS[/color] [COLOR=blue]T[/color] 
[COLOR=blue]ON[/color] J.PreReq = [COLOR=blue]T[/color].CourseNo) [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] tblSubCourse [COLOR=blue]ON[/color] J.PreReq = tblSubCourse.SubCourseNumber 
[COLOR=blue]WHERE[/color] (((J.JobNumber)= [COLOR=red]'101'[/color])) 
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] J.JobNumber, J.PreReq, [COLOR=blue]T[/color].CourseNo, [COLOR=blue]T[/color].EmpNo, tblSubCourse.SubCourseName, 
tblSubCourse.ExpirationInMonths, IIf([COLOR=#FF00FF]isnull[/color]([Disqualified]),0,[[COLOR=blue]t[/color]].[Disqualified])

 

[RiverGuy]

Firstly, you cannot uses the IF statement in T-SQL as part of the body of your select query. You can use CASE. IF is to control the flow of the different parts of your query.

Could you explain the business rules for the red part in plain English? I think it would be easier to translate it that way than trying to work through that Access syntax.

 

[gmmastros]

Try this:

Case When [T].[EmpNo] Is NULL 
     Then '19000101'
     Else Case When ExpirationInMonths = 0 
               Then '20991231'
               Else DateAdd(Month, ExpirationInMonths, CourseDate)
               End
     End As ExpireDate

There's another IIF function in the group by clause, so you'll need to fix that too.

[tt][blue]IIf(isnull([Disqualified]),0,[t].[Disqualified])[/blue][/tt]

Most of the time, IIF is replaced with Case/When, like this.

[tt][blue]Case When Disqualified Is NULL
Then 0
Else Disqualified
End
[/blue][/tt]

In this situation, though, you can use IsNull or Coalesce, like this...

[tt][blue]
Coalesce(Disqualified, 0)
[/tt][blue]

I encourage you to do a little research on Case/When and the IsNull & Coalesce functions.

If there's anything here that doesn't make sense, let me know and I will explain further.





-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jadams0173]

Thanks all for the replies. I should have noted that I know that IIF is not valid. I usually try to take queries and re-write them on step a time. This time I put in what I had done in SQL and left the rest as jet for "completeness", sorry for the confusion.

George I will give your suggestion a try. I've been reading about COALESCE and CASE/WHEN and have used each in converting other queries. I just got lost in the parenthesis(LIPS) and logic I guess.

RiverGuy
The part in red checks to see if an employee number is in table T. If it's not set the expiration to 01/01/1900 to flag the class has never been take. If it is then if the expiration length is 0 then set the expiration date at 12/31/2099 to show that the class never expires, else calculate the expiration date of the class.

 

[RiverGuy]

So then it looks like you don't need your MAX function and the GROUP BY.

 

[jadams0173]

Here is my new query that almost gives me the results needed. I've tried it with and without the MAX and GROUP BY and get the same results. The problem is that I only want the most recent time the class has been taken, not every occurence.

[COLOR=blue]SELECT[/color] J.JobNumber, J.PreReq, [COLOR=blue]T[/color].CourseNo, [COLOR=blue]T[/color].EmpNo, [COLOR=#FF00FF]MAX[/color]([COLOR=blue]T[/color].CourseDate) [COLOR=blue]AS[/color] TakenOn, 
tblSubCourse.SubCourseName, tblSubCourse.ExpirationInMonths, 

[COLOR=blue]Case[/color] [COLOR=blue]When[/color] [[COLOR=blue]T[/color]].[EmpNo] [COLOR=blue]Is[/color] NULL 
     [COLOR=blue]Then[/color] [COLOR=red]'19000101'[/color]
     [COLOR=blue]Else[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] ExpirationInMonths = 0 
               [COLOR=blue]Then[/color] [COLOR=red]'20991231'[/color]
               [COLOR=blue]Else[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], ExpirationInMonths, CourseDate)
          [COLOR=blue]End[/color]
[COLOR=blue]End[/color] [COLOR=blue]As[/color] [ExpireDate],
[COLOR=#FF00FF]Coalesce[/color] ([Disqualified],0)[COLOR=blue]AS[/color] Disqual
[COLOR=blue]FROM[/color] (tblJobReq [COLOR=blue]AS[/color] J [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] * [COLOR=blue]FROM[/color] tblTrainingRecords [COLOR=blue]WHERE[/color] EmpNo=[COLOR=red]'06388'[/color]) [COLOR=blue]AS[/color] [COLOR=blue]T[/color] 
[COLOR=blue]ON[/color] J.PreReq = [COLOR=blue]T[/color].CourseNo) [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] tblSubCourse [COLOR=blue]ON[/color] J.PreReq = tblSubCourse.SubCourseNumber 
[COLOR=blue]WHERE[/color] (((J.JobNumber)= [COLOR=red]'101'[/color])) 
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] J.JobNumber, J.PreReq, [COLOR=blue]T[/color].CourseNo, [COLOR=blue]T[/color].EmpNo, tblSubCourse.SubCourseName, [COLOR=blue]T[/color].CourseDate,
tblSubCourse.ExpirationInMonths, [COLOR=#FF00FF]Coalesce[/color] ([Disqualified],0)

Here is some of the data that was returned that I would like excluded.

JobNo PreReq CourseNo EmpNo [red]TakeOn[/red]
101 1017 1017 06388 2004-06-10 00:00:00.000
101 1017 1017 06388 2006-11-03 00:00:00.000
[red]101 1017 1017 06388 2007-12-14 00:00:00.00[/red]

I only want the record in [red] red [/red]

 

[jadams0173]

I think I've got it sorted out. I'll post back after some more testing.