CODEWITHSUNDEEP
sql-servert-sql
PassionateProgrammer
PassionateProgrammer

Reputation: 864

SQL Server: split a column value into two separate columns based on another column

I have a table Access:

logId   empid   empname  inout   tim  
----------------------------------------------------    
230361   0100     XYZ     0      2015-08-01 10:00:03
230362   0106     XYZ     0      2015-08-01 10:30:00
230363   0100     XYZ     1      2015-08-01 12:00:00

which records each employee's in time and out time. inout=0 means in and inout=1 means out

I would like to create a table as below from this table

empid  empname     timIn                 timOut
-------------------------------------------------------------    
0100     XYZ       2015-08-01 10:00:03   2015-08-01 12:00:00
0106     XYZ       2015-08-01 10:30:00

First I tried case as follows:

select 
    empid, empname, inout,
    case when inout = 0 then tim end as 'timIn',
    case when inout = 1 then tim end as 'timout'

But NULLs were a problem the result was 

0100  xyz       2015-08-01 10:00:03   NULL
0100  xyz       NULL                  2015-08-01 12:00:00

Second I tried PIVOT, but the problem was I had to use an aggregate function. I need all in-out times and cannot take an aggregate of that.

Is there any alternative way to get the desired result?

Upvotes: 2

Views: 402

Answers (3)

DhruvJoshi
DhruvJoshi

Reputation: 17126

update: Based on comment that I should improve this query to take all dates data and not just last date's data, updated query simply includes a new date column

select empid,empname,d,[0] as [timin],[1] as [timOut]
from
(select empid,empname, cast(tim as DATE)as d,inout,tim from tbl) s
pivot
(max(tim) for inout in ([0],[1]))p

updated fiddle link http://sqlfiddle.com/#!6/f1bc7/1

try PIVOT query like this:

select empid,empname,[0] as [timin],[1] as [timOut]
from
(select empid,empname,inout,tim from tbl) s
pivot
(max(tim) for inout in ([0],[1]))p

added SQL fiddle link http://sqlfiddle.com/#!6/6c3bf/1

Upvotes: 1

GarethD
GarethD

Reputation: 69749

You can use APPLY, in conjunction with TOP 1 and the correct ORDER BY to get the next out event after each in event

SELECT  i.empID,
        i.empname,
        TimeIn = i.tim,
        TimeOut = o.tim
FROM    Access AS i
        OUTER APPLY
        (   SELECT  TOP 1 tim
            FROM    Access AS o
            WHERE   o.EmpID = i.EmpID
            AND     o.InOut = 1
            AND     o.tim > i.tim
            ORDER BY o.Tim
        ) AS o
WHERE   i.InOut = 0;

So you are simply selecting all in events (table aliased i), then for each in event, finding the next out event, if there is not one, then the time out field will be null.

FULL WORKING EXAMPLE

DECLARE @Access TABLE (LogID INT NOT NULL, EmpID CHAR(4) NOT NULL, empname VARCHAR(50), InOut BIT NOT NULL, tim DATETIME2 NOT NULL);
INSERT @Access (LogID, EmpID, empname, InOut, tim)
VALUES
    (230361, '0100', 'XYZ', 0, '2015-08-01 10:00:03'),
    (230362, '0106', 'XYZ', 0, '2015-08-01 10:30:00'),
    (230363, '0100', 'XYZ', 1, '2015-08-01 12:00:00');

SELECT  i.empID,
        i.empname,
        TimeIn = i.tim,
        TimeOut = o.tim
FROM    @Access AS i
        OUTER APPLY
        (   SELECT  TOP 1 tim
            FROM    @Access AS o
            WHERE   o.EmpID = i.EmpID
            AND     o.InOut = 1
            AND     o.tim > i.tim
            ORDER BY o.Tim
        ) AS o
WHERE   i.InOut = 0;

Upvotes: 3

Alan Hinton
Alan Hinton

Reputation: 559

So what I think you want to do is find the first time out after each time in. The following SQL should do that.

Select 
empid,
empname,
tim as timein 
(select top 1 tim 
 from my_table outTimes 
 where outTimes.inout = 1 and 
       outTimes.empid = inTimes.empid and 
       outTimes.tim > inTimes.tim 
 orderby outTimes.tim asc
) as timeout
from my_table inTimes
when inout=0

The critical bit here is the orderby asc and the top 1. This is what gives you the next time in the table.

Upvotes: 1

Related Questions