Result Query ?? ,,, VERY URGENT

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 2005 Forums

Transact-SQL (2005)

Result Query ?? ,,, VERY URGENT

Author

Topic

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-02-25 : 03:51:10

Hi ,

I m need of buildding a query to get the result like this

1. Table and its data
Create table TS
( EmpID INT ,
TimeIN DateTime,
TimeOut DateTime)

insert into TS
(Empid , TimeIN , TimeOUT)
select 1 , '2008-02-25 10:05:38.380' ,'2008-02-25 11:15:38.380'
union all
select 1 , '2008-02-25 11:25:38.380' ,'2008-02-25 12:10:38.380'
union all
select 1 , '2008-02-25 13:25:38.380' ,'2008-02-25 15:10:38.380'
union all
select 2 , '2008-02-25 10:25:38.380' ,'2008-02-25 12:10:38.380'
union
select 1 , '2008-02-25 13:00:38.380' ,'2008-02-25 16:10:38.380'

2. The expected Result is

EmpID TIMEIN TIMEOUT TimeIN TIMEOUT TIMEIN TIMEOUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2008-02-25 10:05:38.380 2008-02-25 11:15:38.380 2008-02-25 11:25:38.380 2008-02-25 12:10:38.380 2008-02-25 13:25:38.380 2008-02-25 15:10:38.380

i.e result should be include

a Empid for a partular date and the all the TimeIN And TimOUT should the listed in Columwise .... means if an Empid has 4 logins and 4 logouts then it should have one record for that employee with 4 colums for logins and 4 columns for logouts....

Its urgent and Tahnks in advance if at alll anybody explore thru it and find a suitable solution for this proble

Thanks & Regards

ARD

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 04:08:30

[code]select EmpID,LEFT(dl.datelist,LEN(dl.datelist)-1)
FROM (SELECT DISTINCT EmpID FROM TS) t
CROSS APPLY(SELECT CONVERT(varchar(25),TimeIn,121)+','+CONVERT(varchar(25),TimeOut,121) + ',' AS [text()]
FROM TS
WHERE EmpID=t.EmpID
FOR XML PATH(''))dl(datelist)
output
----------------------
EmpID
----------- -----------------------------------------------------------------------------------------------------------------------------------------------
1 2008-02-25 10:05:38.380,2008-02-25 11:15:38.380,2008-02-25 11:25:38.380,2008-02-25 12:10:38.380,2008-02-25 13:25:38.380,2008-02-25 15:10:38.380
2 2008-02-25 10:25:38.380,2008-02-25 12:10:38.380,2008-02-25 13:00:38.380,2008-02-25 16:10:38.380

[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 04:09:41

I think OP wants a CROSSTAB, not a CSV result.

E 12°55'05.25"
N 56°04'39.16"

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-02-25 : 04:15:34

Yep Peso .... I do need a CROSS TABL OP...so that a login and logout for a particular date may be variable 2 , 3 or 4 or so on...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 04:17:55

Do you have a maximum number of logins?

E 12°55'05.25"
N 56°04'39.16"

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-02-25 : 04:23:01

Normally its below 10 ...

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-25 : 06:15:05

Hi,

Try this

Drop Table #Temp

Declare @Temp table
(
EmpID INT ,
TimeIN DateTime,
TimeOut DateTime
)

insert into @Temp (Empid , TimeIN , TimeOUT)
select 1 , '2008-02-25 10:05:38.380' ,'2008-02-25 11:15:38.380'
union all
select 1 , '2008-02-25 11:25:38.380' ,'2008-02-25 12:10:38.380'
union all
select 1 , '2008-02-25 13:25:38.380' ,'2008-02-25 15:10:38.380'
union all
select 2 , '2008-02-25 10:25:38.380' ,'2008-02-25 12:10:38.380'
union
select 1 , '2008-02-25 13:00:38.380' ,'2008-02-25 16:10:38.380'

Select * Into #Temp
From (Select ROW_NUMBER() OVER( Partition By EmpId ORDER BY Empid ) AS 'RowNumber', Empid ,TimeIn, 'TimeIn' As Time From @Temp Union
Select ROW_NUMBER() OVER(Partition By EmpId ORDER BY Empid ) AS 'RowNumber', Empid ,TimeOUT, 'TimeOut' As TOUT From @Temp )A

--Select * From #Temp

Declare @Sql Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @Sql = @Sql + ', Min (Case when RowNumber = ' + Cast (RowNumber As Varchar(100)) + ' Then TimeIn End ) As "' + Time+'"'
From (Select Distinct RowNumber, Time From #Temp)A

Select @str = 'Select Empid '+@Sql +' From #Temp Group By Empid '
Exec(@str )

aravindt77
Posting Yak Master

120 Posts

Posted - 2008-02-25 : 07:12:01

Thanks Ranganath !!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources