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 | |||