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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Attendance CrossJoin Query

Author  Topic 

creativitymind
Starting Member

2 Posts

Posted - 2011-04-08 : 06:27:01
Hi,

I have a data like in the below format.
EmpID Date Intime OutTime
1 1/4/2011 2011-04-01 07:57:00 2011-04-01 17:06:00
2 1/4/2011 2011-04-01 07:57:00 2011-04-01 17:06:00
1 2/4/2011 2011-04-02 07:57:00 2011-04-02 17:06:00
2 2/4/2011 NULL NULL


I Need a SQL Server 2008 Query to populate the above data into the below format.

EmpID 1inTime 1OutTime 2InTime 2OutTime
1 07:57:00 17:06:00 07:57:00 17:06:00
2 07:57:00 17:06:00 NULL NULL

Please send me the query. It's an Urgent. Thanks in Advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-08 : 07:30:55
How many days will this span? If it is over a period of a year, and if there is one in and out each day, it would result in over 700 columns in the output. But, assuming that you are just looking for only maximum two ins and outs, you can do this:
with cte as
(
select *,
row_number() over (partition by EmpId order by Intime) as row_Id
from YourTable
)
select
EmpId,
max(case when row_id = 1 then Intime end) as FirstIntime,
max(case when row_id = 1 then Outtime end) as FirstOuttime,
max(case when row_id = 2 then Intime end) as FirstIntime,
max(case when row_id = 2 then Outtime end) as FirstOuttime
from
cte
group by
empId
order by
EmpId

Untested code alert
Go to Top of Page

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2011-04-08 : 07:32:29
Maybe this be correct:

SELECT EmpID,
MAX(CASE WHEN rnk = 1 THEN CONVERT(VARCHAR(8), Intime, 108) END) AS [1inTime],
MAX(CASE WHEN rnk = 1 THEN CONVERT(VARCHAR(8), OutTime, 108) END) AS [1outTime],
MAX(CASE WHEN rnk = 2 THEN CONVERT(VARCHAR(8), Intime, 108) END) AS [2inTime],
MAX(CASE WHEN rnk = 2 THEN CONVERT(VARCHAR(8), OutTime, 108) END) AS [2outTime]
FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmpID
ORDER BY Date ASC) AS rnk
FROM TableName
) AS D
GROUP BY EmpID
ORDER BY EmpID;


______________________
Go to Top of Page

creativitymind
Starting Member

2 Posts

Posted - 2011-04-08 : 07:57:24
Thanks a lot for your help
Go to Top of Page
   

- Advertisement -