Some programming code:
USE [Bridge]
GO
/****** Object: StoredProcedure [dbo].[AutoTermAuto] Script Date: 1/17/2023 10:14:34 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[AutoTermAuto]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DELETE FROM [Bridge].[dbo].[AutoTerm]
Where ClientID is not null
-- Insert statements for procedure here
DROP TABLE IF EXISTS #ATermLastPaydate
DROP TABLE IF EXISTS #ATermfinal
----ANY ACTIVE EMPLOYEE REMOVE FROM HISTORY
DELETE [Bridge].[dbo].[AutoTermHistory]
FROM
[Bridge].[dbo].[AutoTermHistory] h INNER JOIN [Welland_Export_Cloud].[dbo].[EMPLOYEE_COM] ec
ON h.ClientID = RIGHT('000000' + CAST(ec.Client_ID as varchar (6)), 6)
AND h.EE_ID = ec.EE_ID
AND h.LastHireDate = ec.EE_Last_Hire_Date
WHERE (ec.EE_Status_Code <> 'T'
AND h.EE_Sort_Name is not null)
--------------------------------------------------
SELECT DISTINCT
e.Client_ID
,a.ClientName
,e.EE_Status_Code
,e.EE_ID
,e.EE_Last_Hire_Date
,a.AutoTermDays
,a.PayRepID
,MAX(p.Pay_Date) as lastMoneypaydate
INTO #ATermLastPaydate
FROM [Welland_Export_Cloud].[dbo].[EMPLOYEE_COM] e
LEFT JOIN [Bridge].[dbo].[AutoTermSet] a
ON a.ClientID = e.Client_ID
LEFT JOIN [Welland_Export_Cloud].[dbo].[EPV] p
ON p.Client_ID = e.Client_ID AND p.EE_ID = e.EE_ID AND p.Tot_Earn_Amt > 0
WHERE e.EE_Status_Code <> 'T'
AND a.AutoTermYesNo = 1
--AND e.EE_ID = 'H48473'
GROUP BY
e.Client_ID
,a.ClientName
,e.EE_Status_Code
,e.EE_ID
,e.EE_Last_Hire_Date
,a.AutoTermDays
,a.PayRepID
----------------------------------------------------
SELECT DISTINCT
RIGHT('000000' + CAST(t.Client_ID as varchar (6)), 6) as Client_ID
-- ,t.ClientName
,t.EE_Status_Code
,t.EE_ID
,ep.EE_Sort_Name
,t.EE_Last_Hire_Date
,p.Pay_Period_End_Date as PayPeriodEnd
,t.lastMoneypaydate
,CASE WHEN t.lastMoneypaydate is NULL THEN DATEDIFF(DAY, t.EE_Last_Hire_Date, GETDATE())
ELSE DATEDIFF(DAY, t.lastMoneypaydate, GETDATE()) END as DayWithoutPay
--,DATEDIFF(DAY, t.lastMoneypaydate, GETDATE()) as DayWithoutPay
,t.AutoTermDays as AutotermSetDay
,t.AutoTermDays - DATEDIFF(DAY, t.lastMoneypaydate, GETDATE()) as dayleftToAutoterm
,b.Ben_Status_Code as EStatus
,t.PayRepID
INTO #ATermfinal
FROM #ATermLastPaydate t
LEFT JOIN [Welland_Export_Cloud].[dbo].[EPV] p
ON p.Client_ID = t.Client_ID AND p.EE_ID = t.EE_ID AND t.lastMoneypaydate = p.Pay_Date
LEFT JOIN [Welland_Export_Cloud].[dbo].[EMPLOYEE_PER] ep
ON ep.u2_id = t.EE_ID
LEFT JOIN (SELECT Client_ID ,EE_ID ,Ben_Status_Code
FROM [Welland_Export_Cloud].[dbo].[EMPLOYEE_BENEFIT_ENROLLMENTS]
WHERE Ben_Status_Code = 'A'
GROUP BY Client_ID
,EE_ID
,Ben_Status_Code) b
ON b.Client_ID = t.Client_ID AND b.EE_ID = t.EE_ID
--AND p.Tot_Earn_Amt > 0
-- WHERE t.EE_ID IN ('A05194','A81586', 'H75209', 'A70609', 'E15062')
-------------------------------------
UPDATE #ATermfinal
SET DayWithoutPay = DATEDIFF(Day, EE_Last_Hire_Date, GETDATE())
WHERE EE_Last_Hire_Date > lastMoneypaydate
UPDATE #ATermfinal
SET dayleftToAutoterm = AutotermSetDay - DayWithoutPay
UPDATE #ATermfinal
SET EStatus = ec.gg
FROM
(SELECT
Client_ID
,EE_ID
,EE_Last_Hire_Date
,ONBOARD_INPROGRESS
,'ONBOARDING' as gg
FROM [Welland_Export_Cloud].[dbo].[EMPLOYEE_COM]
WHERE ONBOARD_INPROGRESS = 'Y' ) ec
WHERE ec.Client_ID = #ATermfinal.Client_ID
AND ec.EE_ID = #ATermfinal.EE_ID
AND ec.EE_Last_Hire_Date = #ATermfinal.EE_Last_Hire_Date
UPDATE #ATermfinal
SET EStatus = ec.gg
FROM
(SELECT
Client_ID
,EE_ID
,EE_Last_Hire_Date
,'Family Member' as gg
FROM [Welland_Export_Cloud].[dbo].[EMPLOYEE_COM]
WHERE EE_Type_Code = 'FM' ) ec
WHERE ec.Client_ID = #ATermfinal.Client_ID
AND ec.EE_ID = #ATermfinal.EE_ID
AND ec.EE_Last_Hire_Date = #ATermfinal.EE_Last_Hire_Date
UPDATE #ATermfinal
SET EStatus = 'ActiveBenefit'
WHERE Estatus = 'A'
UPDATE #ATermfinal
SET PayPeriodEnd = DATEADD(day, 1, EE_Last_Hire_Date)
WHERE PayPeriodEnd is NULL
INSERT INTO [Bridge].[dbo].[AutoTerm]
([ClientID]
,[EmpStatus]
,[EE_ID]
,[EE_Sort_Name]
,[EE_Last_Hire_Date]
,[LastPaidPeriodEnd]
,[LastInvoiceDate]
,[DaysWithoutPay]
,[AutotermSet]
,[DaysLeftToAutoterm]
,[BenStatus]
,PayRepID
)
SELECT t.Client_ID
,t.EE_Status_Code
,t.EE_ID
,t.EE_Sort_Name
,t.EE_Last_Hire_Date
,t.PayPeriodEnd
,t.lastMoneypaydate
,t.DayWithoutPay
,t.AutotermSetDay
,t.dayleftToAutoterm
,t.EStatus
,t.PayRepID
FROM #ATermfinal t
WHERE t.dayleftToAutoterm < 3
END
DROP TABLE IF EXISTS #ATermLastPaydate
DROP TABLE IF EXISTS #ATermfinal