SELECT * FROM #Request.prefix_db_lookup#.Agency where status = 'active'; SELECT distinct e.employee_id,e.email,el.License_Type,el.Expiration_Date,e.First_Name,e.Last_Name,e.Daily_Mail_Date,e.Weekly_Mail_Date,e.2Week_Mail_Date,e.Monthly_Mail_Date FROM #Request.prefix_db_lookup#.Agency a inner join #Request.prefix_db_lookup#.Employee e on a.agency_id = e.agency_id inner join #Request.prefix_db_agency#.EmployeeLicenses el on el.employee_id = e.employee_id where a.agency_id = #agency_Id# and e.status='active' and e.email is not null and e.email <> '' and (curdate()> el.expiration_date) and e.email LIKE '%@%'; SELECT distinct e.employee_id,e.email,el.License_Type,el.Expiration_Date,e.First_Name,e.Last_Name,e.License_Mail_Date FROM #Request.prefix_db_lookup#.Agency a inner join #Request.prefix_db_lookup#.Employee e on a.agency_id = e.agency_id inner join #Request.prefix_db_agency#.EmployeeLicenses el on el.employee_id = e.employee_id where a.agency_id = #agency_Id# and e.status='active' and e.email is not null and e.email <> '' and (curdate()< el.expiration_date) and e.email LIKE '%@%'; Select s.Visit_Date,s.StartTime,s.EndTime,p.First_Name_M0040,p.Last_Name_M0040 from #Request.prefix_db_agency#.ScheduleItems s inner join #Request.prefix_db_agency#.patient p on s.patient_id = p.patient_id where s.employee_id = #employee_id# and s.Visit_Date > #Expiration_Date# Update #Request.prefix_db_lookup#.Employee SET License_Mail_Date = #Expiration_Date# WHERE employee_id = #employee_id#

Hi #Last_Name# #First_Name#,

Your #License_Type# License is due to expired on #DateFormat(ParseDateTime(Expiration_Date),"dd-mm-yyyy")# Your visits for the coming week are

Visit Date    Start Time     End Time     Patient Name    Paitent(s) Not Available.

#DateFormat(ParseDateTime(Visit_Date),"dd-mm-yyyy")#   #StartTime#     #EndTime#     #Last_Name_M0040# #First_Name_M0040#

Thanks & Regards, HomeCare Team.

Select s.Visit_Date,s.StartTime,s.EndTime,p.First_Name_M0040,p.Last_Name_M0040 from #Request.prefix_db_agency#.ScheduleItems s inner join #Request.prefix_db_agency#.patient p on s.patient_id = p.patient_id where s.employee_id = #employee_id# and s.Visit_Date > #Expiration_Date# Update #Request.prefix_db_lookup#.Employee SET Daily_Mail_Date = date_add(curdate(), INTERVAL 1 DAY) WHERE employee_id = #employee_id# Update #Request.prefix_db_lookup#.Employee SET Daily_Mail_Date = date_add(curdate(), INTERVAL 1 DAY) WHERE employee_id = #employee_id# Update #Request.prefix_db_lookup#.Employee SET Weekly_Mail_Date = date_add(curdate(), INTERVAL 7 DAY) WHERE employee_id = #employee_id# Update #Request.prefix_db_lookup#.Employee SET Weekly_Mail_Date = date_add(curdate(), INTERVAL 7 DAY) WHERE employee_id = #employee_id# Update #Request.prefix_db_lookup#.Employee SET 2Week_Mail_Date = date_add(curdate(), INTERVAL 14 DAY) WHERE employee_id = #employee_id# Update #Request.prefix_db_lookup#.Employee SET 2Week_Mail_Date = date_add(curdate(), INTERVAL 14 DAY) WHERE employee_id = #employee_id# Update #Request.prefix_db_lookup#.Employee SET Monthly_Mail_Date = date_add(curdate(), INTERVAL 30 DAY) WHERE employee_id = #employee_id# Update #Request.prefix_db_lookup#.Employee SET Monthly_Mail_Date = date_add(curdate(), INTERVAL 30 DAY) WHERE employee_id = #employee_id#

Hi #Last_Name# #First_Name#,

Your #License_Type# License is expired on #DateFormat(ParseDateTime(Expiration_Date),"dd-mm-yyyy")# Your visits for the coming week are

Visit Date    Start Time     End Time     Patient Name

#DateFormat(ParseDateTime(Visit_Date),"dd-mm-yyyy")#    #StartTime#     #EndTime#     #Last_Name_M0040# #First_Name_M0040#

Thanks & Regards, HomeCare Team.