技术请求：筛选年份为2的员工评估记录并生成指定输出表格

Hey there! Let's work through this SQL problem for your employee evaluation system. From what you've shared, you need to pull records for employees who've filled out forms marked with year 2, and display their username plus all columns where the year value is 2. Here are a couple of tailored approaches:

Approach 1: Show Year Values & Form Status #

This query uses joins to pull in all relevant form data, then uses CASE statements to clearly flag which forms have a year value of 2. It also filters to only include employees who have at least one form with year 2:

SELECT 
    p.profile_id,
    p.profile_fname AS username,
    -- Flag if lesson observation form has year 2
    CASE WHEN lo.lo_year = 2 THEN 'Completed (Year 2)' ELSE 'Not Completed' END AS lesson_observation_status,
    -- Flag if self-assessment form has year 2
    CASE WHEN sa.sa_year = 2 THEN 'Completed (Year 2)' ELSE 'Not Completed' END AS self_assessment_status,
    -- Flag if dept head appraisal form has year 2
    CASE WHEN sahd.sahd_year = 2 THEN 'Completed (Year 2)' ELSE 'Not Completed' END AS dept_head_appraisal_status
FROM 
    dbo.profiles p
LEFT JOIN 
    dbo.lesson_observation lo ON p.profile_id = lo.profile_id
LEFT JOIN 
    dbo.self_assess sa ON p.profile_id = sa.profile_id
LEFT JOIN 
    dbo.staffappraisal_head_dept sahd ON p.profile_id = sahd.profile_id
WHERE 
    lo.lo_year = 2 
    OR sa.sa_year = 2 
    OR sahd.sahd_year = 2
GROUP BY 
    p.profile_id, p.profile_fname, lo.lo_year, sa.sa_year, sahd.sahd_year;

Notes for Approach 1: #

• LEFT JOIN ensures we don't exclude employees who only filled out one type of form
• The WHERE clause filters out any employees who haven't filled out any Year 2 forms
• GROUP BY prevents duplicate records if an employee has multiple entries in a single form table

Approach 2: Efficient Existence Check #

If you only need to confirm whether an employee has submitted a Year 2 form (rather than seeing the exact year value), this EXISTS-based query is more performant and avoids join-related duplicates:

SELECT 
    p.profile_id,
    p.profile_fname AS username,
    -- Check if Year 2 lesson observation exists
    EXISTS(SELECT 1 FROM dbo.lesson_observation lo WHERE lo.profile_id = p.profile_id AND lo.lo_year = 2) AS has_year2_lesson_observation,
    -- Check if Year 2 self-assessment exists
    EXISTS(SELECT 1 FROM dbo.self_assess sa WHERE sa.profile_id = p.profile_id AND sa.sa_year = 2) AS has_year2_self_assessment,
    -- Check if Year 2 dept head appraisal exists
    EXISTS(SELECT 1 FROM dbo.staffappraisal_head_dept sahd WHERE sahd.profile_id = p.profile_id AND sahd.sahd_year = 2) AS has_year2_dept_appraisal
FROM 
    dbo.profiles p
WHERE 
    EXISTS(SELECT 1 FROM dbo.lesson_observation lo WHERE lo.profile_id = p.profile_id AND lo.lo_year = 2)
    OR EXISTS(SELECT 1 FROM dbo.self_assess sa WHERE sa.profile_id = p.profile_id AND sa.sa_year = 2)
    OR EXISTS(SELECT 1 FROM dbo.staffappraisal_head_dept sahd WHERE sahd.profile_id = p.profile_id AND sahd.sahd_year = 2);

Notes for Approach 2: #

• EXISTS checks are faster because they stop searching as soon as a matching record is found
• Results will show 1 (true) or 0 (false) for each form type, making it easy to scan which forms each employee completed for Year 2

Pick the approach that best fits how you want to present the data in your system!

内容的提问来源于stack exchange，提问作者YoungSQLee