CODEWITHSUNDEEP
sqlsql-server
Abdul Ali
Abdul Ali

Reputation: 1937

get records that have only 1 record per group

we have attendance db data as follows (sql server)

empid       date          type
1           01-Jan         In
1           01-Jan         Out
2           01-Jan         In
3           01-Jan         In
3           01-Jan         Out

How can we get records that have only 1 record per date per employee (in above case empid 2 for 01-jan)?

The query should simply list all records of employees that have only single type for a day.

EDIT

The result set should be a bit more specific: show all employee who only have "In" for a date but no "Out"

Upvotes: 0

Views: 960

Answers (5)

Red Devil
Red Devil

Reputation: 2393

This will work if we have ID with 1 IN OR 1 OUT as well

Declare @t table (empid int,date varchar(50),types varchar(50))

insert into @t values (1,'01-Jan','IN')
insert into @t values (1,'01-Jan','OUT')
insert into @t values (2,'01-Jan','IN')
insert into @t values (3,'01-Jan','OUT')
insert into @t values (4,'01-Jan','OUT')

select * from @t a
where not exists (select 1 from @t b where a.empid=b.empid and a.types!=b.types)

Upvotes: 0

Gowtham Ag
Gowtham Ag

Reputation: 187

The Solution is Very Simple, You can use 'DISTINCT' function. Query Should be as, 

SELECT DISTINCT empid FROM attendance

This will return only 1 record per date per employee.

For Your Reference, Check it out- https://www.techonthenet.com/sql_server/distinct.php

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use window functions:

select t.*
from (select t.*,
             count(*) over (partition by empid, date) as cnt
      from t
     ) t
where cnt = 1;

You can also use aggregation:

select empid, date, max(type) as type
from t
group by empid, date
having count(*) = 1;

Upvotes: 3

JohnHC
JohnHC

Reputation: 11195

Use Having

select empid, date, count(*)
from Mytable
group by empid, date
having count(*) = 1

You can use this to get the full line:

select t1.*
from MyTable t1
inner join 
(
    select empid, date, count(*)
    from Mytable
    group by empid, date
    having count(*) = 1
) t2
on t1.empid = t2.empid
and t1.date = t2.date

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

Use a correlated subquery

select * from tablename a
where not exists (select 1 from tablename b where a.empid=b.empid and  a.date=b.date and type='Out')

OR 

select empid, date,count(distinct type)
from tablename
group by empid,date
having count(distinct type)=1

Upvotes: 1

Related Questions