t-sql i am transforming data
Posted
by
João Pedro Portelinha
on Stack Overflow
See other posts from Stack Overflow
or by João Pedro Portelinha
Published on 2012-06-06T16:19:37Z
Indexed on
2012/06/06
16:40 UTC
Read the original article
Hit count: 460
Filed under:
sql-server-2008
I am transforming data from this legacy table: MovTime (IdMov INT, IdPerson NVARCHAR(20), Date1 datetime, Type1 nvarchar(30) )
IdMov IdPerson Date1 Type
----------- -------------------- ----------------------- ------------------------------
1 David 2012-06-01 09:00:00.000 Entered
2 David 2012-06-01 12:30:00.000 Exit
3 David 2012-06-01 14:00:00.000 Entered
4 David 2012-06-01 18:30:00.000 Exit
5 Kim 2012-06-02 09:00:00.000 Entered
6 Kim 2012-06-02 12:00:00.000 Exit
... I want the result to be the following:
IdPerson Data Total Time
---------- ---------- ----------
David 2012-06-01 08:00:00
Kim 2012-06-02 03:00:00
T-SQL
declare @WK_TABLE TABLE (IdMov INT, IdPerson NVARCHAR(20), Date1 datetime, Type1 nvarchar(30))
Insert into @WK_TABLE values(1,'David', '2012-06-01 09:00', 'Entered')
Insert into @WK_TABLE values(2,'David', '2012-06-01 12:30', 'Exit')
Insert into @WK_TABLE values(3,'David', '2012-06-01 14:00', 'Entered')
Insert into @WK_TABLE values(4,'David', '2012-06-01 18:30', 'Exit')
Insert into @WK_TABLE values(5,'Kim', '2012-06-02 09:00', 'Entered')
Insert into @WK_TABLE values(6,'Kim', '2012-06-02 12:00', 'Exit')
select * from @WK_TABLE
Can someone help me?
© Stack Overflow or respective owner
