SQL Query needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query needed

Author

Topic

srinivasreddy.k1
Starting Member

2 Posts

Posted - 2010-03-27 : 01:42:58

Hi,

I have a table as below

Empid Date Inout
1 25/02/2010 9:30 0(In)
1 25/02/2010 10:30 1(Out)
1 25/02/2010 11:30 1
1 25/02/2010 12:30 0
1 25/02/2010 13:30 1
1 25/02/2010 14:30 0
1 25/02/2010 15:30 1

I need the final table in the belwo format:

Empid InDate OutDate
1 25/02/2010 9:30 25/02/2010 10:30
1 NULL 25/02/2010 11:30
1 25/02/2010 12:30 25/02/2010 13:30
1 25/02/2010 14:30 25/02/2010 15:30

Here the employee has checkout twice but not checked in
so i need the indate null, shows that he has not checked in properly

Mainly i need to do the query based on onout sequence if sequence is in 1,0 alternately then all is fine, but if we have same values with 1,1 or 0,0 then problem

i have done it for some extend as below where the employee checks in and checks out correctly.

SELECT *
FROM
(
select a.empid from
(select ROW_NUMBER() OVER (ORDER BY dt ASC) Id,empid,dt,tid from Trans
where convert(varchar,updatedon ,111) = '2010/03/15' and empname = 'Srinivas Reddy'
and inout = 0 ) a ,
(select ROW_NUMBER() OVER (ORDER BY dt ASC) Id,empid,dt,tid from Trans
where convert(varchar,updatedon ,111) = '2010/03/15' and empname = 'Srinivas Reddy'
and inout = 1) b
where a.empid = b.empid
and a.id =b.id
) r

Srinivas Reddy K

srinivasreddy.k1
Starting Member

2 Posts

Posted - 2010-03-27 : 01:44:09

I am using SQL Server 2005

Srinivas Reddy K

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 02:23:34

[code];With CTE(RowNo,Empid,Date,Inout)
AS
(SELECT ROW_NUMBER() OVER(PARTITION BY Empid,Inout ORDER BY Date ASC) AS RowNo,Empid,Date,Inout FROM Table
)

SELECT COALESCE(c1.Empid,c2.Empid) AS Empid,
c1.Date AS InDate,
c2.Date AS OutDate
FROM CTE c1
FULL OUTER JOIN CTE c2
ON c2.Empid=c1.Empid
AND c2.RowNo=c1.RowNo
AND c1.Inout =0
AND c2.Inout =1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources