Please see the code below.
The query categorises report according to the criteria in the code.
The ploblem is that it defaults to 'New'
I would like to be able to explicitly test for 'New' and introduce a new category 'Error' as the default.
i.e.'New' is true when the student_code does not appear in the dqi_exclusions table or the DQI_All_Audit_Reports_History table.
Is it possible to incorporate this into the existing code?
Any help appreciated.
SQL Server 2005.
Code:
DECLARE @date datetime
SET @date = getdate()
set datefirst 7
select distinct
A.*,
case
when H.Report_ID is not null then 'Continuing'
when E.Report_ID is not null then 'Continuing - Exclusion'
when EC.Report_ID is not null then 'Data Change'
else 'New' end as Issue_Category, -- Categorise each issues depending on whether it appears in the Exclusion table
null as Current_Audit,
case
when H.Report_ID is not null then H.Issue_Date
when E.Report_ID is not null then H.Issue_Date
when EC.Report_ID is not null then @date
else @date end as Issue_Date -- Return the date that the issue was first found in the DQI - used for ageing
from
staging..DQI_All_Audit_Reports_Single_Run as A
left join staging..DQI_Audit_Reports as AR on AR.Report_ID = A.Report_ID
-- Join exclusions as an exact match
LEFT JOIN Staging..DQI_Exclusions as E
ON E.Report_ID = A.Report_ID
AND case when AR.Exclusion_Level = 'STU' then left(E.Student_Code,8) else E.Student_Code end = case when AR.Exclusion_Level = 'STU' then left(A.Student_Code,8) else A.Student_Code end
AND case when AR.Exclusion_Level = 'SCE' then E.SCE_Sequence else 1 end = case when AR.Exclusion_Level = 'SCE' then A.SCE_Sequence else 1 end
AND E.Log_Date <= A.Audit_Date
AND isnull(E.Field_Value,'') = isnull(A.Field_Value,'')
AND isnull(E.Log_Remove,0) <> 1
-- Join exclusions as a match on all but the field_value (indicates Data Change category)
LEFT JOIN Staging..DQI_Exclusions AS EC
ON EC.Report_ID = A.Report_ID
AND case when AR.Exclusion_Level = 'STU' then left(EC.Student_Code,8) else EC.Student_Code end = case when AR.Exclusion_Level = 'STU' then left(A.Student_Code,8) else A.Student_Code end
AND case when AR.Exclusion_Level = 'SCE' then EC.SCE_Sequence else 1 end = case when AR.Exclusion_Level = 'SCE' then A.SCE_Sequence else 1 end
AND EC.Log_Date <= A.Audit_Date
AND isnull(E.Field_Value,'') <> isnull(A.Field_Value,'')
AND isnull(EC.Log_Remove,0) <> 1
-- Join the history table in order to find the date that the issue was first found in the DQI
LEFT JOIN (select * from Staging..DQI_All_Audit_Reports_History as hist where hist.Audit_Date = (select max(Audit_Date) from Staging..DQI_All_Audit_Reports_History) ) as H
on H.report_year = A.report_year
and H.Report_ID = A.Report_ID
AND H.Student_Code = A.Student_Code
AND H.SCE_Sequence = A.SCE_Sequence
AND isnull(H.Field_Value,'') = isnull(A.Field_Value,'')
where
-- Don't show issues where the audit report has been switched off
case when isnull(temporary_remove_report,0) = 1 then 1
when isnull(pre_release,0) = 1 then 1
when (case AR.Run_Type
when null then 'True'
when 'Never' then 'False'
when 'Daily' then 'True'
when 'Sunday' then
case when datepart(dw,@date) = 1 then 'True' else 'False' end
when 'Last of Month' then
case when DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0)) = datepart(dy,@date) then 'True' else 'False' end
when 'Range' then
case
when AR.Run_Start > AR.Run_End then
case
when datepart(dy,@date) > AR.Run_End and datepart(dy,@date) < AR.Run_Start then 'False'
else 'True'
end
when datepart(dy,@date) < AR.Run_Start then 'before start'
when datepart(dy,@date) > AR.Run_End then 'after start'
else 'True'
end
else 'True' end = 'False') then 1
else 0 end = 0
order by report_id,student_code