View: dbo.Client_W2_Verification_Date_View
View definition
-- A client is verified only when ALL of its employees are verified & its “Date Verified” is the latest verification
-- date of any employee. This produces verified clients and their verified date.
--If a client doesn't have a result row for a given year_verified, then it's not verified for that year.
--If a client has no employees, then it is considered unverified and will not have a verification date
CREATE VIEW dbo.Client_W2_Verification_Date_View
AS
SELECT e.client_KEY
,v.year_verified
-- This returns a NULL if any of the employee verification_date fields are NULL, otherwise it returns the max verification_date found.
-- 06\06\2079 is used because that's the largest date that the SQL smalldate type supports.
,CASE
WHEN MAX(COALESCE(v.verification_date, '06/06/2079')) = '06/06/2079'
THEN NULL
ELSE MAX(v.verification_date)
END AS verification_date
FROM dbo.Employee_W2_Verification_Dates v
INNER JOIN dbo.Employee e ON v.employee_KEY = e.employee_KEY
--do not include template employees
WHERE e.employee_type_KEY != 2
GROUP BY e.client_KEY
,v.year_verified