Help with a SQL Query - SQL Server Forums

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)

Help with a SQL Query

Author

Topic

th02b0
Starting Member

6 Posts

Posted - 2015-04-10 : 04:46:02

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-10 : 08:15:21

[code]
;WITH cteSample
AS
(SELECT [EmpCode],[Time],[In/Out]
FROM (VALUES ('001','2013-10-02 06:54:00',0)
,('001','2013-10-02 11:35:00',1)
,('001','2013-10-02 13:29:00',0)
,('001','2013-10-02 17:03:00',1)
,('001','2013-10-02 20:50:00',0)
,('001','2013-10-12 06:02:00',1)
,('001','2013-10-12 11:32:00',0)
,('001','2013-10-12 13:17:00',0)
,('001','2013-10-12 17:00:00',0)
,('001','2013-10-22 06:57:00',1)
,('001','2013-10-22 11:46:00',1)
,('001','2013-10-22 13:21:00',0)
,('001','2013-10-22 17:01:00',1)) AS alias ([EmpCode],[Time],[In/Out])
)
,DIFER
AS (SELECT
[EmpCode],[Time],[In/Out]
,ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] /*, DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0)*/ ORDER BY [Time])
- ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] /*, DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0)*/ ,[In/Out] ORDER BY [Time])
AS GRP
/*,ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] ,DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0) ORDER BY [Time]) AS RN_DAY
,ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] ,DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0), [In/Out] ORDER BY [Time]) AS RN_DAY_IO

,ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] ,DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0) ORDER BY [Time])
- ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] ,DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0), [In/Out] ORDER BY [Time])
AS GRP2*/
,DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0) AS DT
FROM
cteSample AS A
)
--SELECT * FROM DIFER ORDER BY Time
,GRP
AS (SELECT
[EmpCode]
,Time
,[In/Out]
,GRP
,DT
FROM DIFER B
)
, FINAL_SELECT
AS (
SELECT
A.EmpCode
,A.Time
,A.[In/Out]
,A.GRP
,ROW_NUMBER() OVER(PARTITION BY A.EmpCode, A.GRP,A.[In/Out]
ORDER BY CASE WHEN A.[In/Out] = 0 THEN A.Time ELSE '0' END DESC
, CASE WHEN A.[In/Out] = 1 THEN A.Time ELSE '0' END ASC ) AS RN
,DT
FROM GRP AS A
)
--SELECT * FROM FINAL_SELECT
SELECT
DISTINCT
A.EmpCode
--,B.Time
--,C.Time
,CASE WHEN A.[In/Out] = 1 THEN A.Time ELSE C.Time END AS TimeIn
,CASE WHEN A.[In/Out] = 0 THEN A.Time ELSE B.Time END AS TimeOut
FROM FINAL_SELECT A
OUTER APPLY
(SELECT TOP (1)
B.Time
FROM FINAL_SELECT B
WHERE
A.Time < B.Time
AND A.DT =B.DT
AND RN = 1
ORDER BY B.Time ASC)B
OUTER APPLY
(SELECT TOP (1)
C.Time
FROM FINAL_SELECT C
WHERE
A.Time > C.Time
AND A.DT =C.DT
AND RN = 1
ORDER BY C.Time DESC)C
WHERE RN = 1
ORDER BY 2,3
[/code]

result :
[code]
EmpCode 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
[/code]

sabinWeb MCP

th02b0
Starting Member

6 Posts

Posted - 2015-04-15 : 00:26:29

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

Result:

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----2013-10-01 06:43:00---2013-10-01 11:30:00 <------ ERROR (002----NULL---2013-10-01 11:30:00)
003----NULL-------------------2013-10-01 06:43:00

Thank you !

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-15 : 01:26:38

[code]
;WITH cteSample
AS
(SELECT [EmpCode],[Time],[In/Out]
FROM (VALUES ('001','2013-10-02 06:54:00',0)
,('001','2013-10-02 11:35:00',1)
,('001','2013-10-02 13:29:00',0)
,('001','2013-10-02 17:03:00',1)
,('001','2013-10-02 20:50:00',0)
,('001','2013-10-12 06:02:00',1)
,('001','2013-10-12 11:32:00',0)
,('001','2013-10-12 13:17:00',0)
,('001','2013-10-12 17:00:00',0)
,('001','2013-10-22 06:57:00',1)
,('001','2013-10-22 11:46:00',1)
,('001','2013-10-22 13:21:00',0)
,('001','2013-10-22 17:01:00',1)
,('002','2013-10-01 11:30:00',0)
,('002','2013-10-01 06:47:00',0)
,('003','2013-10-01 06:43:00',0)) AS alias ([EmpCode],[Time],[In/Out])
)
,DIFER
AS (SELECT
[EmpCode],[Time],[In/Out]
,ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] ORDER BY [Time])
- ROW_NUMBER()OVER(PARTITION BY A.[EmpCode],[In/Out] ORDER BY [Time])
AS GRP
,DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0) AS DT
FROM
cteSample AS A
)
--SELECT * FROM DIFER ORDER BY Time
,GRP
AS (SELECT
[EmpCode]
,Time
,[In/Out]
,GRP
,DT
FROM DIFER B
)
, FINAL_SELECT
AS (
SELECT
A.EmpCode
,A.Time
,A.[In/Out]
,A.GRP
,ROW_NUMBER() OVER(PARTITION BY A.EmpCode, A.GRP,A.[In/Out]
ORDER BY CASE WHEN A.[In/Out] = 0 THEN A.Time ELSE '0' END DESC
, CASE WHEN A.[In/Out] = 1 THEN A.Time ELSE '0' END ASC ) AS RN
,DT
FROM GRP AS A
)
--SELECT * FROM FINAL_SELECT
SELECT
DISTINCT
A.EmpCode
--,B.Time
--,C.Time
,CASE WHEN A.[In/Out] = 1 THEN A.Time ELSE C.Time END AS TimeIn
,CASE WHEN A.[In/Out] = 0 THEN A.Time ELSE B.Time END AS TimeOut
FROM FINAL_SELECT A
OUTER APPLY
(SELECT TOP (1)
B.Time
FROM FINAL_SELECT B
WHERE
A.Time < B.Time
AND A.DT =B.DT
AND RN = 1
AND A.EmpCode = B.EmpCode
ORDER BY B.Time ASC)B
OUTER APPLY
(SELECT TOP (1)
C.Time
FROM FINAL_SELECT C
WHERE
A.Time > C.Time
AND A.DT =C.DT
AND RN = 1
AND A.EmpCode = C.EmpCode
ORDER BY C.Time DESC)C
WHERE RN = 1
ORDER BY 1,2,3
[/code]

output:
[code]
EmpCode 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
[/code]

sabinWeb MCP

th02b0
Starting Member

6 Posts

Posted - 2015-04-15 : 03:43:12

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

Result:

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-02 15:53:00
004----2013-10-02 20:30:00---NULL

NOT SEE:
004----NULL-------------------2013-10-01 11:30:00

Thank you !

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-15 : 04:15:09

Sorry, I don't understand the logic.
Can you provide more details about this.

Why for 3 Output type


004----2013-10-01 06:47:00---False
004----2013-10-01 11:30:00---False
004----2013-10-02 15:53:00---False

should be 2 records displayed?
and if will be more then 3 consecutive out type records(like 5) what should be the display.
Maybe this needs to be rewrite

sabinWeb MCP

th02b0
Starting Member

6 Posts

Posted - 2015-04-15 : 05:06:16

Logic:

2013-10-02 (Emp:001) ----NULL----06:54________11:35----Work----13:29________17:03----Work----20:50

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-02 15:53:00
004----2013-10-02 20:30:00---NULL

NOT SEE:
004----NULL-------------------2013-10-01 11:30:00

if will be more then 3 consecutive out type records(like 5) what should be the display.
can you rewrite. Thank you !

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-15 : 07:59:26

[code]
;WITH cteSample
AS
(SELECT [EmpCode],[Time],[In/Out]
FROM (VALUES ('001','2013-10-02 06:54:00',0)
,('001','2013-10-02 11:35:00',1)
,('001','2013-10-02 13:29:00',0)
,('001','2013-10-02 17:03:00',1)
,('001','2013-10-02 20:50:00',0)
,('001','2013-10-12 06:02:00',1)
,('001','2013-10-12 11:32:00',0)
,('001','2013-10-12 13:17:00',0)
,('001','2013-10-12 17:00:00',0)
,('001','2013-10-22 06:57:00',1)
,('001','2013-10-22 11:46:00',1)
,('001','2013-10-22 13:21:00',0)
,('001','2013-10-22 17:01:00',1)
,('002','2013-10-01 11:30:00',0)
,('002','2013-10-01 06:47:00',0)
,('003','2013-10-01 06:43:00',0)
,('004','2013-10-01 06:47:00',0)
,('004','2013-10-01 11:30:00',0)
,('004','2013-10-02 15:53:00',0)
,('004','2013-10-02 20:30:00',1)) AS alias ([EmpCode],[Time],[In/Out])
)
,DIFER
AS (SELECT
[EmpCode],[Time],[In/Out]
,ROW_NUMBER()OVER(PARTITION BY A.[EmpCode] ORDER BY [Time])
- ROW_NUMBER()OVER(PARTITION BY A.[EmpCode],DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0),[In/Out] ORDER BY [Time])
AS GRP
,DATEADD(DAY,DATEDIFF(DAY,0,A.[Time]),0) AS DT
FROM
cteSample AS A
)
--SELECT * FROM DIFER ORDER BY [EmpCode],Time

,GRP
AS (SELECT
[EmpCode]
,Time
,[In/Out]
,GRP
,DT
FROM DIFER B
)
, FINAL_SELECT
AS (
SELECT
A.EmpCode
,A.Time
,A.[In/Out]
,A.GRP
,ROW_NUMBER() OVER(PARTITION BY A.EmpCode, A.GRP,A.[In/Out]
ORDER BY CASE WHEN A.[In/Out] = 0 THEN A.Time ELSE '0' END DESC
, CASE WHEN A.[In/Out] = 1 THEN A.Time ELSE '0' END ASC ) AS RN
,DT
FROM GRP AS A
)
--SELECT * FROM FINAL_SELECT ORDER BY 1

SELECT
DISTINCT
A.EmpCode
--,B.Time
--,C.Time
,CASE WHEN A.[In/Out] = 1 THEN A.Time ELSE C.Time END AS TimeIn
,CASE WHEN A.[In/Out] = 0 THEN A.Time ELSE B.Time END AS TimeOut
FROM FINAL_SELECT A
OUTER APPLY
(SELECT TOP (1)
B.Time
FROM FINAL_SELECT B
WHERE
A.Time < B.Time
AND A.DT =B.DT
AND RN = 1
AND A.EmpCode = B.EmpCode
ORDER BY B.Time ASC)B
OUTER APPLY
(SELECT TOP (1)
C.Time
FROM FINAL_SELECT C
WHERE
A.Time > C.Time
AND A.DT =C.DT
AND RN = 1
AND A.EmpCode = C.EmpCode
ORDER BY C.Time DESC)C
WHERE RN = 1
ORDER BY 1,2,3
[/code]

output:
[code]
EmpCode 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
[/code]

sabinWeb MCP

th02b0
Starting Member

6 Posts

Posted - 2015-04-15 : 23:11:04

Thank you very much !

stepson
Aged Yak Warrior

545 Posts

Posted - 2015-04-16 : 01:04:53

Welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources