CREATE PROCEDURE sp_appEmployeeCoverageEnrollment@newclass int,@employee_ssn int,@coverageID int,@coverageTierID int,@networkChoiceID int,@CoverageTypeID int,@Amount int,@Multiple floatasdeclare @EmployeeCoverageEnrollmentID as intdeclare @EFCOLifeNetwork as intDeclare EmpExists_cursor CURSOR Fast_FORWARD FORselect a.EmployeeCoverageEnrollmentIDfrom EmployeeCoverageEnrollment as a with (nolock) inner join coverage as b with (nolock) on a.coverageID = b.coverageIDwhere employee_ssn = @employee_ssn and b.coverageTypeID = @coverageTypeID
OPEN EmpExists_cursorFETCH NEXT FROM EmpExists_cursor into @EmployeeCoverageEnrollmentIDif @@FETCH_STATUS = 0 begin delete from EmployeeCoverageEnrollment where EmployeeCoverageEnrollmentID = @EmployeeCoverageEnrollmentID endclose EmpExists_Cursordeallocate EmpExists_cursor
set @EFCOLifeNetwork = 0if @coverageid=686 or (@coverageID>=688 and @coverageid<=699) begin set @EFCOLifeNetwork = @networkChoiceID set @networkChoiceID = 0 end ------added for increase of amount on 11/22/2005if @EFCOLifeNetwork=1 begin if @newclass=358 or @newclass=359 or @newclass=360 begin if @Amount*0.1>10000 begin set @Amount=@Amount+@Amount*0.1 end else begin set @Amount=@Amount+10000 end end if @newclass=361 begin set @Amount=@Amount+10000 end end -----added for increase of amount on 11/22/2005insert into EmployeeCoverageEnrollment(employee_ssn, coverageID, coverageTierID, networkChoiceID, amount, MultipleOfSalary, EFCOLifeNetwork)values(@employee_ssn, @coverageID, @coverageTierID, @networkChoiceID, @amount, @Multiple, @EFCOLifeNetwork)GO
select distinct aa.EMPLOYEE_SSN,aa.viseligdate,aa.vistermdate,aa.fname,aa.lname,aa.dob from (
SELECT EMPLOYEE_SSN,fname,lname, (case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then viseligdate else (select max(temptermthrudate)+1 as temptermthrudate from (SELECT temptermthrudate1 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate1<>'12/31/2020' union SELECT temptermthrudate2 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate2<>'12/31/2020' union SELECT temptermthrudate3 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate3<>'12/31/2020') as temp) end) as viseligdate, (case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then deneligdate else (select max(temptermthrudate)+1 as temptermthrudate from (SELECT temptermthrudate1 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate1<>'12/31/2020' union SELECT temptermthrudate2 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate2<>'12/31/2020' union SELECT temptermfromdate3 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' and temptermthrudate3<>'12/31/2020') as temp) end) as deneligdate, vistermdate,dentermdate,dob FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='493112829 ' union
SELECT EMPLOYEE_SSN,dependent_fname as fname,dependent_lname as lname, (case when temp_term_thru_date='12/31/2020' then viseligdate else temp_term_thru_date+1 end) as viseligdate, (case when temp_term_thru_date='12/31/2020' then deneligdate else temp_term_thru_date+1 end) as deneligdate, vistermdate,dentermdate,dob FROM Tbl_dependents WHERE EMPLOYEE_SSN='493112829'
) as aa where viseligdate<>'12/31/2020'group by aa.viseligdate,aa.vistermdate,aa.EMPLOYEE_SSN,aa.fname,aa.lname,AA.dob order by aa.viseligdate,aa.vistermdate,AA.dob
SELECT EMPLOYEE_SSN,dependent_fname as fname,dependent_lname as lname, (case when temp_term_thru_date='12/31/2020' then viseligdate else temp_term_thru_date+1 end) as viseligdate, (case when temp_term_thru_date='12/31/2020' then deneligdate else temp_term_thru_date+1 end) as deneligdate, vistermdate,dentermdate,dob FROM Tbl_dependents WHERE EMPLOYEE_SSN='493112829' and viseligdate<>'12/31/2020'
viseligdatevistermdate
select * FROM Tbl_dependents WHERE EMPLOYEE_SSN='493112829'
sp_slcMemoMaster 0,2900
select * from Tbl_Memo
select distinct aa.EMPLOYEE_SSN,aa.medeligdate,aa.medtermdate,aa.fname,aa.lname,aa.dob from (SELECT EMPLOYEE_SSN,fname,lname,(case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then medeligdate else (select max(temptermthrudate)+1 as temptermthrudate from(SELECT temptermthrudate1 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate1<>'12/31/2020' union SELECT temptermthrudate2 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate2<>'12/31/2020' union SELECT temptermthrudate3 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate3<>'12/31/2020') as temp) end) as medeligdate,(case when temptermthrudate1='12/31/2020' and temptermthrudate2='12/31/2020' and temptermthrudate3='12/31/2020' then deneligdate else (select max(temptermthrudate)+1 as temptermthrudate from(SELECT temptermthrudate1 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate1<>'12/31/2020' union SELECT temptermthrudate2 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate2<>'12/31/2020' union SELECT temptermfromdate3 as temptermthrudate FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' and temptermthrudate3<>'12/31/2020') as temp) end) as deneligdate,medtermdate,dentermdate,dob FROM Tbl_employee_eligibility WHERE EMPLOYEE_SSN='" & session("ssn") & "' union SELECT EMPLOYEE_SSN,dependent_fname as fname,dependent_lname as lname,(case when temp_term_thru_date='12/31/2020' then medeligdate else temp_term_thru_date+1 end) as medeligdate,(case when temp_term_thru_date='12/31/2020' then deneligdate else temp_term_thru_date+1 end) as deneligdate,medtermdate,dentermdate,dob FROM Tbl_dependents WHERE EMPLOYEE_SSN='" & session("ssn") & "') as aa group by aa.medeligdate,aa.medtermdate,aa.EMPLOYEE_SSN,aa.fname,aa.lname,AA.dob order by aa.medeligdate,aa.medtermdate,AA.dob