Help with a SQL Query

Question

Help with a SQL Query

th02b0

SSC Rookie

Points: 35
More actions
April 15, 2015 at 3:28 am

#398329

EmpCode..........Time...............In/Out
001----2013-10-02 06:54:00---False
001----2013-10-02 11:35:00---True
001----2013-10-02 13:29:00---False
001----2013-10-02 17:03:00---True
001----2013-10-02 20:50:00---False
001----2013-10-12 06:02:00---True
001----2013-10-12 11:32:00---False
001----2013-10-12 13:17:00---False
001----2013-10-12 17:00:00---False
001----2013-10-22 06:57:00---True
001----2013-10-22 11:46:00---True
001----2013-10-22 13:21:00---False
001----2013-10-22 17:01:00---True
002----2013-10-01 11:30:00---False
002----2013-10-01 06:47:00---False
003----2013-10-01 06:43:00---False
004----2013-10-01 06:47:00---False
004----2013-10-01 11:30:00---False
004----2013-10-02 15:53:00---False
004----2013-10-02 20:30:00---True
OUT PUT:
Emp.....TimeIn................................TimeOut
001----NULL-------------------2013-10-02 06:54:00
001----2013-10-02 11:35:00---2013-10-02 13:29:00
001----2013-10-02 17:03:00---2013-10-02 20:50:00
001----2013-10-12 06:02:00---2013-10-12 17:00:00
001----2013-10-22 06:57:00---2013-10-22 13:21:00
001----2013-10-22 17:01:00---NULL
002----NULL-------------------2013-10-01 11:30:00
003----NULL-------------------2013-10-01 06:43:00
004----NULL-------------------2013-10-01 11:30:00
004----NULL-------------------2013-10-02 15:53:00
004----2013-10-02 20:30:00---NULL
Thank you !

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

If you want better chances of an answer, post your question using insert statements for your data and post desired results in a readable way.

Example:

Sample data:

DECLARE @mytable TABLE(

EmpCode VARCHAR(34)

, Time datetime

, InOut VARCHAR(5)

);

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-02 06:54:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-02 11:35:00.000','True');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-02 13:29:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-02 17:03:00.000','True');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-02 20:50:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-12 06:02:00.000','True');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-12 11:32:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-12 13:17:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-12 17:00:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-22 06:57:00.000','True');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-22 11:46:00.000','True');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-22 13:21:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('001','2013-10-22 17:01:00.000','True');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('002','2013-10-01 11:30:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('002','2013-10-01 06:47:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('003','2013-10-01 06:43:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('004','2013-10-01 06:47:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('004','2013-10-01 11:30:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('004','2013-10-02 15:53:00.000','False');

INSERT INTO @mytable(EmpCode,Time,InOut) VALUES ('004','2013-10-02 20:30:00.000','True');

Desired output:

+-----+---------------------+---------------------+

| Emp | TimeIn | TimeOut |

+-----+---------------------+---------------------+

| 001 | NULL | 2013-10-02 06:54:00 |

| 001 | 2013-10-02 11:35:00 | 2013-10-02 13:29:00 |

| 001 | 2013-10-02 17:03:00 | 2013-10-02 20:50:00 |

| 001 | 2013-10-12 06:02:00 | 2013-10-12 17:00:00 |

| 001 | 2013-10-22 06:57:00 | 2013-10-22 13:21:00 |

| 001 | 2013-10-22 17:01:00 | NULL |

| 002 | NULL | 2013-10-01 11:30:00 |

| 003 | NULL | 2013-10-01 06:43:00 |

| 004 | NULL | 2013-10-01 11:30:00 |

| 004 | NULL | 2013-10-02 15:53:00 |

| 004 | 2013-10-02 20:30:00 | NULL |

+-----+---------------------+---------------------+

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

spaghettidba

SSC Guru

Points: 105732

April 15, 2015 at 4:02 am

#1791033

Edit: removed

-- Gianluca Sartori

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

Using Gianlucas setup

WITH Grouped AS (

SELECT EmpCode,[Time],InOut,

ROW_NUMBER() OVER(PARTITION BY EmpCode ORDER BY [Time]) -

ROW_NUMBER() OVER(PARTITION BY EmpCode,InOut ORDER BY [Time]) AS rnDiff

FROM @mytable),

Grouped2 AS (

SELECT EmpCode,

InOut,

CASE WHEN InOut = 'true' THEN MIN([Time]) ELSE MAX([Time]) END AS [Time],

ROW_NUMBER() OVER(PARTITION BY EmpCode ORDER BY MIN([Time])) - CASE WHEN InOut = 'True' THEN 0 ELSE 1 END AS Offset

FROM Grouped

GROUP BY EmpCode,InOut,rnDiff)

SELECT EmpCode,

MIN(CASE WHEN InOut = 'True' THEN [Time] END) AS TimeIn,

MAX(CASE WHEN InOut = 'False' THEN [Time] END) AS TimeOut

FROM Grouped2

GROUP BY EmpCode,Offset

ORDER BY EmpCode,TimeIn,TimeOut;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537