SQLTeam.com | Weblogs | Forums

Explicit query


#1

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


#2

not sure this is what you mean. I just extended the opening CASE expression:

,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'
WHEN EC.StudentCode is null or h.studentcode is null
THEN 'NEW'
ELSE 'Error'