SELECT DISTINCT
[c].[id]
,[c].[fullname]
,[c].[email]
,[p].[position_date]
,[as].[id] AS [assesment_id]
,[as].[title] AS [assesment_name]
,[ccatpm].[month]
,[ccatpm].[day]
,[ccatpm].[hour]
,[ccatpm].[minute]
FROM
[cc_agat_type_position_matrixs] AS [ccatpm]
LEFT JOIN [cc_agat_type_position_matrix] AS [ccatpm_doc] ON [ccatpm].[id] = [ccatpm_doc].[id]
CROSS APPLY [ccatpm_doc].[data].nodes('cc_agat_type_position_matrix/assessments/assessment') AS [_ccatpm_doc](data)
INNER JOIN [assessments] AS [as] ON [_ccatpm_doc].[data].value('(./assessment_id)[1]', 'bigint') = [as].[id]
INNER JOIN [position_commons] AS [cp] ON [ccatpm].[position_common_id] = [cp].[id]
INNER JOIN [positions] AS [p] ON [p].[position_common_id] = [cp].[id]
INNER JOIN [collaborators] AS [c] ON [p].[basic_collaborator_id] = [c].[id]
LEFT JOIN [test_learnings] AS [tl] ON [tl].[person_id] = [c].[id] AND [as].[id] = [tl].[assessment_id]
LEFT JOIN [active_test_learnings] AS [atl] ON [tl].[person_id] = [c].[id] AND [as].[id] = [atl].[assessment_id]
WHERE
[atl].[id] IS NULL
AND [tl].[id] IS NULL
AND [ccatpm].[month] IS NOT NULL
AND [ccatpm].[day] IS NOT NULL
AND [ccatpm].[hour] IS NOT NULL
AND [ccatpm].[minute] IS NOT NULL
AND [p].[position_date] >= (Select DATEADD(day,-30,GETDATE()))
AND [c].[email] IS NOT NULL