Available to Schedule SQL

--Available to Schedule
IF @OccurrenceDate <> ''

BEGIN
    SELECT p.Id, p.LastName + ', '+ p.NickName Name, p.Email, gr.Name AS [Group Role], rs.Value AS [Record Status], cs.Value AS [Connection Status],@OccurrenceDate as OccurrenceDate
    FROM GroupMember gm
    JOIN [Group] g ON gm.GroupId = g.Id
    JOIN Person p ON gm.PersonId = p.Id
    JOIN DefinedValue rs ON p.RecordStatusValueId = rs.Id
    JOIN DefinedValue cs ON p.ConnectionStatusValueId = cs.Id
    JOIN GroupTypeRole gr ON gm.GroupRoleId = gr.Id
    -- Use this to exclude people based black out dates
    LEFT JOIN (
        SELECT pa.PersonId, excl.GroupId
        FROM PersonScheduleExclusion excl
        JOIN PersonAlias pa WITH(NOLOCK) ON excl.PersonAliasId = pa.Id
        WHERE CONVERT(DATE, @OccurrenceDate) BETWEEN excl.StartDate AND excl.EndDate
        ) AS ex ON ( ex.GroupId IS NULL OR gm.GroupId = ex.GroupId ) AND p.Id = ex.PersonId
    -- Use this to exclude people who have already been scheduled/requested to serve on the date specified
    LEFT JOIN (
        SELECT pa.PersonId, ao.GroupId
        FROM Attendance att
        JOIN AttendanceOccurrence ao ON att.OccurrenceId = ao.Id
        JOIN PersonAlias pa WITH(NOLOCK) ON att.PersonAliasId = pa.Id
        WHERE
            -- Exclude anyone who has been scheduled, declined a schedule request, or where a request is in process
            -- Those who have not been scheduled have a ScheduledToAttend and RequestedToAttend Set to 0
            ( att.ScheduledToAttend = 1 OR (att.ScheduledToAttend = 0 AND att.RequestedToAttend = 1) OR att.RequestedToAttend = 1 )
            AND CONVERT( DATE, ao.OccurrenceDate ) = CONVERT( DATE, @OccurrenceDate )
        ) a ON gm.GroupId = a.GroupId AND p.Id = a.PersonId
    WHERE CONVERT( VARCHAR(100), g.Guid ) = @Group AND gm.IsArchived = 0
        AND gm.GroupMemberStatus = 1
        AND ex.PersonId IS NULL
        AND a.PersonId IS NULL
    GROUP BY  p.Id, p.LastName, p.NickName, p.Email, gr.Name, rs.Value, cs.Value

    ORDER BY p.LastName, p.NickName
END
ELSE
BEGIN
    SELECT 'Please select an occurrence date' AS Error
END