I am having trouble getting the following query to work properly:
SELECT
tblEmployees.iEmployeeNum AS ID, tblEmployees.tLastName AS SURNAME, tblEmployees.tFirstName AS NAME,
pInt.dtEventReal AS 'ENTRANCE', pIes.dtEventReal AS 'EXIT'
FROM
tblEmployees
LEFT JOIN
(SELECT DISTINCT
dtEventReal,idEmpNum
FROM
tblEvents
WHERE
tDesc LIKE '%Entrance%'
AND CAST(tblEvents.dtEventReal AS DATE) BETWEEN CAST('2014-07-07' AS DATE) AND CAST('2014-07-08' AS DATE)
) AS pInt ON pInt.idEmpNum = tblEmployees.iEmployeeNum
LEFT JOIN
(SELECT DISTINCT
dtEventReal, idEmpNum
FROM
tblEvents
WHERE
tDesc LIKE '%Exit%'
AND CAST(tblEvents.dtEventReal AS DATE) BETWEEN CAST('2014-07-07' AS DATE) AND CAST('2014-07-08' AS DATE)
) AS pIes ON pIes.idEmpNum = tblEmployees.iEmployeeNum
WHERE
iEmployeeNum = 265
AND (CAST(pInt.dtEventReal AS DATE) = CAST(pIes.dtEventReal AS DATE)
OR (DATEPART(hh, pInt.dtEventReal) >= 18
AND CAST(pInt.dtEventReal AS DATE date) = CAST(pIes.dtEventReal-1 AS DATE)))
The result that I get are:
ID SURNAME NAME ENTRANCE EXIT
265 LAST FIRST 2014-07-07 07:56:08.000 2014-07-07 10:21:34.000
265 LAST FIRST 2014-07-07 15:12:17.000 2014-07-07 10:21:34.000
265 LAST FIRST 2014-07-07 07:56:08.000 2014-07-07 16:47:31.000
265 LAST FIRST 2014-07-07 15:12:17.000 2014-07-07 16:47:31.000
265 LAST FIRST 2014-07-08 07:56:34.000 2014-07-08 10:05:39.000
265 LAST FIRST 2014-07-08 14:32:53.000 2014-07-08 10:05:39.000
265 LAST FIRST 2014-07-08 07:56:34.000 2014-07-08 16:46:35.000
265 LAST FIRST 2014-07-08 14:32:53.000 2014-07-08 16:46:35.000
What I am looking for is:
ID NAME SURNAME ENTRANCE EXIT
265 LAST FIRST 2014-07-07 07:56:08.000 2014-07-07 10:21:34.000
265 LAST FIRST 2014-07-07 15:12:17.000 2014-07-07 16:47:31.000
265 LAST FIRST 2014-07-08 07:56:34.000 2014-07-08 10:05:39.000
265 LAST FIRST 2014-07-08 14:32:53.000 2014-07-08 16:46:35.000
L.E. 1 - the last condition is there
OR
(datepart(hh,pInt.dtEventReal)>=18 and cast(pInt.dtEventReal as date)=cast(pIes.dtEventReal-1 as date)))
because we have people that work night shifts and finish the next morning.
L.E. 2 -
the events are being added to the database automatically by a software that gets the events from a controller:
IdAutoEvents tDesc dtEventReal tLastName tFirstName iEmployeeNum
312503 Main_Entrance 2014-07-18 07:24:33.000 SURNAME NAME 265
312742 Main_Exit 2014-07-18 10:31:23.000 SURNAME NAME 265
L.E 3 - Final version based on mxix answer:
SELECT
E.iEmployeeNum ID,
E.tLastName SURNAME,
E.tFirstName NAME,
pInt.dtEventReal AS 'ENTRANCE',
pIes.dtEventReal AS 'EXIT',
pInt.tDesc AS 'Entrance Location',
pIes.tDesc AS 'Exit Location'
FROM tblEmployees E
LEFT JOIN (
SELECT
DISTINCT
dtEventReal,
idEmpNum,
tDesc
FROM tblEvents
WHERE
tDesc LIKE '%Entrance%' AND
CONVERT(DATE,tblEvents.dtEventReal) BETWEEN cast('2014-07-07' as date) AND cast('2014-07-12' as date)
) pInt ON
pInt.idEmpNum = E.iEmployeeNum
OUTER APPLY(
SELECT
TOP 1
dtEventReal,
idEmpNum,
tDesc
FROM tblEvents Ev
WHERE
tDesc LIKE '%Exit%' AND
Ev.idEmpNum = pInt.idEmpNum and
pInt.dtEventReal < Ev.dtEventReal
ORDER BY
dtEventReal ASC
) AS pIes
WHERE
E.IdDepartment in (2,11)
ORDER BY E.tLastName ASC, E.tFirstName, pInt.dtEventReal
Give it a try
DECLARE
@start DATE = '2014-07-07',
@end DATE = '2014-07-07',
@user_id int = 265;
SELECT
E.iEmployeeNum ID,
E.tLastName SURNAME,
E.tFirstName NAME,
pInt.dtEventReal AS 'ENTRANCE',
pIes.dtEventReal AS 'EXIT'
FROM tblEmployees E
LEFT JOIN (
SELECT
DISTINCT
dtEventReal,
idEmpNum
FROM tblEvents
WHERE
tDesc LIKE '%Entrance%' AND
CONVERT(DATE,tblEvents.dtEventReal) BETWEEN @start AND @end
) pInt ON
pInt.idEmpNum = E.iEmployeeNum
OUTER APPLY(
SELECT
TOP 1
dtEventReal,
idEmpNum
FROM tblEvents Ev
WHERE
tDesc LIKE '%Exit%' AND
Ev.idEmpNum = pInt.idEmpNum and
pInt.dtEventReal > Ev.dtEventReal
ORDER BY
dt_EventReal ASC
) AS pIes
WHERE
E.iEmployeeNum = @user_id