How do I compare all the column values based on the Partition by with ID

Question

I have a case where there are many rows for the same ID, I just wanted to know if there is any way to find out whether the given ID includes some specific value or not.

Table 1

ID      Dept      Salary       Flag    Date 
1        IT        5000         N      2017-01-01
1        IT        5000         N      2017-01-02
1        IT        5000         N      2017-01-03
1        IT        5000         N      2017-01-04
1        IT        5000         N      2017-01-05
2        HR        4000         N      2017-01-01
2        HR        4000         N      2017-01-02
2        HR        4000         Y      2017-01-03
2        HR        4000         N      2017-01-04
2        HR        4000         N      2017-01-05
3        Fin       4500         N      2017-01-08
3        Fin       4500         N      2017-01-09
3        Fin       4500         N      2017-01-10
3        Fin       4500         N      2017-01-11
3        Fin       4500         N      2017-01-12
4        Edu       4800         N      2017-02-10
4        Edu       4800         N      2017-02-11
4        Edu       4800         N      2017-02-12
4        Edu       4800         Y      2017-02-13
4        Edu       4800         N      2017-02-14
4        Edu       4800         N      2017-02-15

Expected Result:

ID      Dept      Salary       Flag    Date 
1        IT        5000         N      2017-01-01
1        IT        5000         N      2017-01-02
1        IT        5000         N      2017-01-03
1        IT        5000         N      2017-01-04
1        IT        5000         N      2017-01-05

3        Fin       4500         N      2017-01-08
3        Fin       4500         N      2017-01-09
3        Fin       4500         N      2017-01-10
3        Fin       4500         N      2017-01-11
3        Fin       4500         N      2017-01-12

As IT and Fin don't have Y flag, in any row, I just want this result to be displayed. Is there any way I can find out this information.

Answer 1

try this

select * from @mytable
where id in (
select 
id from @mytable
group by id
having SUM(case when flag='N' then 1 else 0 end) =COUNT(*))