Include indicator of data present, but not include in results

I can’t quite explain it properly, hence the awful title.

As an example, I would have a query that shows customer complaints, of a certain type, and the dates since those complaints. Something like the following:

select * from( select c.firstname, c.lastname , max(ont.date1) as "LastDate", 
DATEDIFF(DAY, MAX(ont.Date1), SYSDATETIME()) AS "Days"

from [ComplaintInfo] ci
inner join [OrderNotes] ont on ont.orderid = ci.orderid
inner join [Customers] c on c.custid = ci.custid
right outer join [CustLive] cl on ont.custidl = cl.custidl
where (ci.typeofcomp = '2' or ci.typeofcomp = '3')
and (ont.answertype <> '2' and ont.answertype <> '3' and ont.answertype <>'4'
group by c.lastname, c.firstname
)   Sub
where Days >= 5
order by Days, sub.lastname asc

This would give something like

John | Smith | 2020-06-03T13:00:00 | 1

Terry | Jones | 2020-05-04T:04:00:00 | 30

However, although I’m wanting typeoforder to NOT be 2 or 3, and I don’t want them to be included in my result set, I would like to know whether or not there have been any orders of those types. So, if for example

John | Smith |2020-06-03T13:00:00 | 1 | Yes

Terry | Jones | 2020-05-04-04:00:00 | 30 | No

Could just be asking a stupid question, but need to know. Thanks.

Answer

You can remove the condition from the where clause and use a conditional expression:

select 
    c.firstname, 
    c.lastname, 
    max(ont.date1) as LastDate 
    datediff(day, max(ont.date1), sysdatetime()) as days,
    max(case when ci.typeofcomp = 2 or ci.typeofcomp = 3 then 'Yes' else 'No' end) hasTypeOfComp_2_Or_3
from [ComplaintInfo] ci
inner join [OrderNotes] ont on ont.orderid = ci.orderid
inner join [Customers] c on c.custid = ci.custid
right outer join [CustLive] cl on ont.custidl = cl.custidl
where ont.answertype <> 2 and ont.answertype <> 3 and ont.answertype <> 4
group by c.lastname, c.firstname

Notes:

• I removed the single quotes around the numbers – since they look like numbers, I assumed that they are stored as such, in which case you want to compare them against literal numbers (if that’s not the case, you can put the quotes back)

• I am suspicious about the right outer join: is this really what you want? If not, just use an inner join