Displaying if clause finds results or not from SQL

I have the following example data in tableexample (im using MSSQL):

ID	date	tag
15551	2021-11-10	1
15551	2021-11-09	0
15551	2021-11-10	1
12123	2021-11-09	1
12123	2021-11-09	1
15551	2021-11-10	1
12123	2021-11-10	1
74141	2021-11-10	1
12345	2021-11-10	1
11111	2021-11-10	1
74141	2021-11-10	1
12345	2021-11-10	0

Now I want to get for a set range of IDs (15551,12123,12345,74141) the information if they contain at least one entry that satisfies the condition: date <> today (2021-11-10) and tag = 1

So my result for this example should look like this:

ID	checkfoundentry
15551	0
12123	1
74141	0
12345	0

Explanation: 12123 and 15551 contain the date from yesterday (2021-11-09), but 15551 contains this date where the tag = 0. So only 12123 has both conditions satisfied for at least one result.

So its easy for me to group them together, but I dont know how to check the condition over the grouped IDs: Select ID, ??? as checkfoundentry from tableexample where ID in (15551,12123,12345,74141) Group By ID

Is it possible to do it this way?

Here is sql that can provide the example data:

Create Table table1 (
colID int,
coldate date,
coltag int
);

Insert Into table1 (colID, coldate, coltag)
values (15551, '2021-11-10', 1),
(15551, '2021-11-09', 0),
(15551, '2021-11-10', 1),
(12123, '2021-11-09', 1),
(12123, '2021-11-09', 1),
(15551, '2021-11-10', 1),
(12123, '2021-11-10', 1),
(74141, '2021-11-10', 1),
(12345, '2021-11-10', 1),
(11111, '2021-11-10', 1),
(74141, '2021-11-10', 1),
(12345, '2021-11-10', 0),
(12345, '2021-11-10', 1)

And here is a specific solution I found could you tell if this is any good?

Select ID, (CASE when (Select sum(Tag) from table1 t where date <> 2021-11-10 and tag = 1 and s.ID = t.ID Group By ID) > 0 then 1 Else 0 END) as checkfoundentry from table1 s Group By ID

Solution

select t.ID ,
(select  count(*)
from tbl t2 
where t2.ID = t.ID and 
t2.date = cast(getdate() as date) and t2.tag = 1) checkfoundentry 
from tbl t 
where t.ID in (15551,12123,12345,74141)

I used innerquery to count the filters you specified ...