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

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.

The query as below
Query 1

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(*))

Query 2

You could use DENSE_RANK() function to allow ranking based on column Dept, Flag

SELECT * FROM <table> WHERE ID IN
(
       SELECT A.ID FROM 
       (
          SELECT *, DENSE_RANK() OVER (order by Dept, Flag) [RN] FROM <table>
       ) A GROUP BY A.ID HAVING COUNT(DISTINCT RN) = 1)

EDIT : If don't want to use of DENSE_RANK() function you could also use of simple case expression condition

SELECT * FROM <table> WHERE ID IN
(
    SELECT ID FROM <table> GROUP BY ID
    HAVING COUNT(DISTINCT CASE WHEN Flag = 'N' THEN 1 ELSE 0 END) = 1
);

Other simple way :

SELECT * FROM <table> WHERE ID NOT IN
(
    SELECT DISTINCT ID FROM <table> WHERE FLAG ='Y'
);

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

Query 3

WITH Flaggen AS 
(
select distinct ID
from Table1
where Flag <> 'Y'
)

select * 
from Table1 
join Flaggen 
on Flaggen.Id = Table1.ID

 

 

Refer links - https://stackoverflow.com/questions/47426916/how-do-i-compare-all-the-column-values-based-on-the-partition-by-with-id/47426980#47426980