I have the below code which works and does what it needs to.
However, I need to manage two exact queries through an IF statement.
Can anyone see a way to do away with the IF ?
IF @ReportClientTypeAbbreviation = 'IA' BEGIN select * from Client CR Inner Join LookUp.ClientType CT on CR.ClientTypeID = CT.ClientTypeID where CR.SubmitDate >=@ReportStartDate and CR.SubmitDate <=@ReportEndDate and CT.ReportClientTypeAbbreviation = 'IA' END ELSE BEGIN select * from Client CR Left Join LookUp.ClientType CT on CR.ClientTypeID = CT.ClientTypeID where CR.SubmitDate >=@ReportStartDate and CR.SubmitDate <=@ReportEndDate and (CT.ReportClientTypeAbbreviation = 'NonIA' or CT.ReportClientTypeAbbreviation is null) END
Advertisement
Answer
You can combine the queries using AND/OR logic which is essential knowledge for SQL queries. The following shows how to do this (untested – but should give you enough to go on):
select * from Client CR left join LookUp.ClientType CT on CR.ClientTypeID = CT.ClientTypeID where CR.SubmitDate >= @ReportStartDate and CR.SubmitDate <= @ReportEndDate and ( (CT.ReportClientTypeAbbreviation = 'IA' and @ReportClientTypeAbbreviation = 'IA') or ( @ReportClientTypeAbbreviation != 'IA' and (CT.ReportClientTypeAbbreviation = 'NonIA' or CT.ReportClientTypeAbbreviation is null) ) )