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

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:

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

CodePudding user response：

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 ...

CodePudding user response：

You can use window functions to count the criteria for each ID

with x as (
    select * ,
        Count(*) over(partition by id) cnt, 
        Sum(tag) over(partition by id) tg, 
        Count(case when date !='2021-11-10' then 1 end) over(partition by id) dt
    from t
    where t.ID in (15551,12123,12345,74141)
)
select distinct id, 
    case when dt>0 and cnt=tg then 1 else 0 end CheckFoundEntry
from x;

CodePudding user response：

You can also use sub-query like below one,

SELECT sq.colID,
    MAX(sq.checkfoundentry) AS checkfoundentry
FROM(
    SELECT t.colID
       , t.coldate
       , t.coltag
       , CASE WHEN t.coldate <> cast(GETDATE() AS date) AND t.coltag = 1 THEN 1 ELSE 0 END AS  checkfoundentry
       --, CASE WHEN ((t.coldate <> cast(getdate() AS date)) OR t.coltag)  1 else 0 end --as checkfoundentry
    FROM dbo.table1 t
    WHERE t.colID IN (15551,12123,12345,74141)
) AS sq
GROUP BY sq.colID