modify a DTS package

[modglin]

We have certifications for managers that consist of several classes. We have a DTS package that compares the Certcode for the manager and if they equal a particular code it inserts the appropriate classes needed to complete the certification. Once they have completed all the courses, the certification is good for 3 years.

We have some individuals that now have their certifications expiring. If they previously took a class and the end date is over 3 years, we would like it to insert the class again with required, however, it is not currently set up that way. I am not sure how to change to code to include that stipulation. We do not want it to insert required if they have taken the class within the last 3 years, only if it has been more than 3 years.

Example

Employee course enddate status certified
Phillip 111 1/1/08 completed 1/15/05 Expired
Phillip 115 6/26/07 completed 1/15/05 Expired
Phillip 119 10/8/04 completed 1/15/05 Expired
Phillip 125 1899-12-30 required 1/15/05 Expired

Class 119 should read required as it has been over three years since it was taken and the certification is expired. Class 125 has not been taken (1899-12-30 is the default for null)

Employee course enddate status certified
Jim 111 1/1/08 completed 10/15/06
Jim 115 6/26/07 completed 10/15/06
Jim 119 10/8/04 completed 10/15/06
Jim 125 1899-12-30 required 10/15/06

Since this certification is not expired Class 119 should remain completed.

Can someone direct me on where the change needs to be made to indicate if expired and if class is older than 3 years? Here is the current code in the DTS package:

IF @jobCode like '240 - R%' OR @jobCode like '240 - M%'
                        BEGIN
                                    IF NOT EXISTS(SELECT * FROM nempcert WHERE empno = @empno AND certcode = '240' AND expdate <> '1899-12-30' AND expdate > GETDATE())
                                    BEGIN
                                                IF EXISTS(SELECT * FROM nempcert WHERE empno = @empno AND certcode = '240' AND expdate < GETDATE())
                                                BEGIN
                                                            SET @expDate = (SELECT expdate FROM nempcert WHERE empno = @empno AND certcode = '240' AND expdate < GETDATE())
                                                END
                                                ELSE
                                                BEGIN
                                                            SET @expDate = '1899-12-30'
                                                END

                                                IF NOT EXISTS(SELECT * FROM nemphist WHERE empno = @empno AND (EndDate > @expDate OR EndDate = '1899-12-30') AND course = @courseCode AND (enrstat = 'COMP  ' or enrstat = 'INPR  ' or enrstat = 'ENROLL' or enrstat = 'Requir'))
                                                BEGIN
                                                            INSERT INTO nemphist_newRecs
                                                            (company,course,empno,enddate,enrstat,firstname,jobrel,lastname,midname,
                                                             p_eeoclass,p_employ,p_jobcode,p_jobgroup,p_jobtitle,p_level1,p_level2,
                                                             p_level3,p_level4,p_level5,required,startdate,chadate,reqdate,starttime,
                                                             sessions,reimbamt,otherfees,misc4,misc3,misc2,misc1,location,jobrelcode,instrcode,
                                                             hours,grade,endtime,duration,daysofweek,credits,coursetype,coursefee,classid,ceu,certcode,
                                                             approved,ampm2,ampm)
                                                            VALUES
                                                            (@company,@courseCode,@empno,'1899-12-30','Required For Cert',@firstname,'Y',
                                                             @lastname,@midName,@peeoclass,@pEmploy,@jobCode,@pJobGroup,@pJobTitle,@pLevel1,
                                                             @pLevel2,@pLevel3,@pLevel4,@pLevel5,'Y','1899-12-30','1899-12-30','1899-12-30',
                                                             '  :  ',1,0,0,0,'','','','Pacific',@jobCode,'other',0,'0%','  :  ',0,'x',0,'In-house',0,0,
                                                             0,0,'N','A','A')
                                                END
                                    END
                        END