Merging two rows within same table

[sudhaMurugesan]

Hi,
I have a table like this.

IOMode Date EmployeeID EmpName
O 2007-02-28 16:46:00.000 FI0001258 M.MANIGANDAN
I 2007-02-28 07:47:00.000 FI0001258 M.MANIGANDAN
Now I want to merge these 2 rows into a single row.The employee is present only if IOMode has both I and O else his presence is invalid. How to check whether the employee is present for that particular date?
Please help.

[frozenmist]

Hi,
You can actually group by the IO,DATE and EMPNAME. IF it returns more than one row after group by then,it means that employee has I and O in his IO .
This would work i think

 
select case  when count(a.cnt)>=2 then 'Present'  else 'not present' end as status from (SELECT COUNT(*) cnt FROM Table WHERE NAME=<somename> and DATE=<somedate>  GROUP BY IO,NAME,DATE ) a
 
 

Cheers

[sudhaMurugesan]

Thank you Mr.Frozenmist, I will try your code too. i tried with exist function and its working.
Here's the code

Create procedure [dbo].[USP_Unpunched_Select]
@EmpCode varchar(20),
@StartDate varchar(20),
@EndDate varchar(20)
AS
select Distinct EmpCode,EmpName,DeptName
from NewView
where EmpCode=@EmpCode
and not Exists
(select IOMODE from NewView where IOMODE = 'I' and
EmpCode=@EmpCode and (PDate between @StartDate and @EndDate)
and not Exists (select IOMODE from NewView where IOMODE = 'O'
and EmpCode=@EmpCode and (PDate between @StartDate and @EndDate)))