I have the following query which will be used to create a report. I must create a report showing zero (0) values if there is no value.
There is a very strong possibility that one or more of these Types will not have any information, therefore, the query will not return any value. My question is, what do I need to do to return 0 (zero) values and print them accordingly? Help is highly appreciated. Thank you.
Code:
PARAMETERS [As Of Date] DateTime;
SELECT "BRIEFED" as Type, T1.ALJName as Judge, 1 as Pet, iif(T1.Consol <> "C", 1 ,0) as Cas, T1.DisplTatno as DTatno, [As Of Date] as aDate, T2.DateBriefComplete as DBComp
FROM tblTracking as T1 INNER JOIN tblCDR as T2 ON T1.Tatno = T2.Tatno
WHERE ( T2.DateBriefComplete < [As Of Date] ) and T2.PetitionClosed = "N" and T2.OnSineDie <> "Y"
UNION SELECT "IN BRIEFING" as Type, T3.ALJName as Judge, 1 as Pet, iif(T3.Consol <> "C", 1 ,0) as Cas, T3.DisplTatno as DTatno, [As Of Date] as aDate, T4.DateBriefComplete as DBComp
FROM tblTracking as T3 INNER JOIN tblCDR as T4 ON T3.Tatno = T4.Tatno
WHERE (isnull(T4.DateBriefComplete) or T4.DateBriefComplete > [As Of Date]) and T4.PetitionClosed = "N" and T4.OnSineDie <> "Y"
UNION SELECT "SINE DIE" as Type, T5.ALJName as Judge, 1 as Pet, iif(T5.Consol <> "C",1,0) as Cas, T5.DisplTatno as DTatno, [As Of Date] as aDate, T6.DateBriefComplete as DBComp
FROM tblTracking as T5 INNER JOIN tblCDR as T6 ON T5.Tatno = T6.Tatno
WHERE T6.OnSineDie = "Y";
