How to do this Query on MSSQL

Question

I need to make a query to show me the entrance(Where TerminalID = 1) and exit (Where TerminalID = 2) per day, but if UserID just have a exit mark for this day, use 08:00:00 on entrance value and if do not have a exit mark, set exit value with 20:00:00 with the same day, i need to know how many days the IDUser was here and how many time per day, that's my table:

SQLFiddle

ID  TransactionTime TerminalID UserID
1   2014-04-01 08:59    1      1
2   2014-04-01 09:09    1      1
3   2014-04-01 09:59    2      1
4   2014-04-01 10:59    1      1
5   2014-04-01 18:59    2      1
6   2014-05-01 08:59    1      1
7   2014-04-01 09:59    1      2
8   2014-04-01 18:59    2      2
9   2014-05-01 08:59    2      2

The result that I need is:

UserID  MarkEntrance    MarkExit          TimeHere
1     2014-04-01 08:59  2014-04-01 09:59  01:00:00
1     2014-04-01 10:59  2014-04-01 18:59  08:00:00
1     2014-05-01 08:59  2014-05-01 20:00  11:01:00

And I can do this:

SELECT TOP 1000
      pna.UserID
      ,Convert(char(10),pna.TransactionTime, 103) as DateOfMark
      ,MarkEntrance = 
CASE WHEN 
(SELECT MIN(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 1 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID ) IS NOT NULL
THEN 
(SELECT MIN(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 1 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID )
ELSE  convert(datetime,Convert(char(10),pna.TransactionTime, 103) + ' 08:00:00')
END
      ,MarkExit = 
CASE WHEN 
(SELECT MAX(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 2 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID ) IS NOT NULL
THEN 
(SELECT MAX(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 2 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID )
ELSE convert(datetime,Convert(char(10),pna.TransactionTime, 103) + ' 20:00:00')
END
      ,TimeHere = 
Convert(char(8),
CASE WHEN 
(SELECT MAX(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 2 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID ) IS NOT NULL
THEN 
(SELECT MAX(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 2 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID )
ELSE convert(datetime,Convert(char(10),pna.TransactionTime, 103) + ' 20:00:00')
END - 
CASE WHEN 
(SELECT MIN(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 1 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID ) IS NOT NULL
THEN 
(SELECT MIN(TransactionTime) FROM [NGAC_AUTHLOG] as na WHERE na.TerminalID = 1 AND Convert(char(10),na.TransactionTime, 103) = Convert(char(10),pna.TransactionTime, 103) AND na.UserID = pna.UserID )
ELSE  convert(datetime,Convert(char(10),pna.TransactionTime, 103) + ' 08:00:00')
END
, 14)
  FROM NGAC_AUTHLOG as pna
  GROUP BY pna.UserID, Convert(char(10),pna.TransactionTime, 103)

But in my query i just know the first entrance and the last exit by UserID, but he can have many marks per day.

(I used MIN and MAX but this way just show one big mark, merging all the marks of the day)

Answer 1

Try something like this. First union part matches all the records for Entrance to exit and orphan entrances. Second part of the union takes care of the orphaned exit records.

Solution 1:

SELECT *, Convert(char(8), MarkExit - MarkEntrance, 14) TimeHere
FROM
(
SELECT
    A.UserID, A.TransactionTime MarkEntrance, ISNULL(ExitTime, DateAdd(hh, 20, CONVERT(VARCHAR(10), A.TransactionTime, 101))) MarkExit 
FROM NGAC_AUTHLOG A
OUTER APPLY
(
SELECT MIN(TransactionTime) ExitTime
  FROM NGAC_AUTHLOG B
  WHERE B.TerminalID = 2
  AND B.UserID = A.UserID
  AND B.TransactionTime > A.TransactionTime
) B
WHERE A.TerminalID = 1

UNION ALL

--This looks for orphan Exit Record
SELECT A.UserID, DateAdd(hh, 8, CONVERT(VARCHAR(10), max(A.TransactionTime), 101)), max(A.TransactionTime)
FROM NGAC_AUTHLOG A
WHERE A.TerminalID = 2 
AND EXISTS
    (SELECT 1 
    FROM
       (SELECT UserID, CONVERT(VARCHAR(10), TransactionTime, 101) T2Date, COUNT(*) T2Count
       FROM NGAC_AUTHLOG
       WHERE TerminalID = 2
       GROUP BY UserID, CONVERT(VARCHAR(10), TransactionTime, 101)) X
       LEFT JOIN
       (SELECT UserID, CONVERT(VARCHAR(10), TransactionTime, 101) T1Date, COUNT(*) T1Count
       FROM NGAC_AUTHLOG
       WHERE TerminalID = 1
       GROUP BY UserID, CONVERT(VARCHAR(10), TransactionTime, 101)) Y
          ON X.UserID = Y.UserID
          AND X.T2Date = Y.T1Date
    WHERE X.T2Count > ISNULL(Y.T1Count,0)
    AND X.UserID = A.UserID
    AND X.T2Date = CONVERT(VARCHAR(10), TransactionTime, 101)
    )
GROUP BY A.UserID, CONVERT(VARCHAR(10), TransactionTime, 101)
) X

Solution 2: Assuming if there is an orphan exit record there is only 1 exit record for that day for that user

SELECT *, Convert(char(8), MarkExit - MarkEntrance, 14) TimeHere
FROM
(
SELECT
    A.UserID, A.TransactionTime MarkEntrance, ISNULL(ExitTime, DateAdd(hh, 20, CONVERT(VARCHAR(10), A.TransactionTime, 101))) MarkExit 
FROM NGAC_AUTHLOG A
OUTER APPLY
(
SELECT MIN(TransactionTime) ExitTime
  FROM NGAC_AUTHLOG B
  WHERE B.TerminalID = 2
  AND B.UserID = A.UserID
  AND B.TransactionTime > A.TransactionTime
) B
WHERE A.TerminalID = 1

UNION ALL
--This looks for orphan Exit Record
SELECT A.UserID, DateAdd(hh, 8, CONVERT(VARCHAR(10), A.TransactionTime, 101)), A.TransactionTime
FROM NGAC_AUTHLOG A
WHERE A.TerminalID = 2 
AND NOT EXISTS
    (SELECT 1 
    FROM NGAC_AUTHLOG B
    WHERE B.TerminalID = 1
     AND A.UserID = B.UserID
      AND CONVERT(VARCHAR(10), A.TransactionTime, 101) = CONVERT(VARCHAR(10), B.TransactionTime, 101))  
) X

SQL Fiddle

Answer 2

Try something like this. Note I haven't run the query, so there might be a simple syntax error in it(mainly in conversions - really not sure about that), if there is an issue, please let me know in a comment.

;WITH minEntranceInDay as(
    SELECT MIN(transactionTime) EntranceTime,Convert(DateTime( Convert(char(10),na.TransactionTime)), 103) Day, UserId    
    FROM NGAC_AUTHLOG WHERE TerminalId = 1
    GROUP BY Convert(DateTime( Convert(char(10),na.TransactionTime)), 103), UserId
), maxExitInDay as(
    SELECT MAX(transactionTime) ExitTime, Convert(DateTime( Convert(char(10),na.TransactionTime)) Day, UserId    
    FROM NGAC_AUTHLOG WHERE TerminalId = 2
    GROUP BY Convert(DateTime( Convert(char(10),na.TransactionTime)), 103), UserId
), usersAndDates as(
    SELECT DISTINCT Convert(DateTime( Convert(char(10),na.TransactionTime))  Day, UserId    
    FROM NGAC_AUTHLOG 
), GroupedData As (
    SELECT ud.UserId, COALESCE(me.minEntranceInDay,Dateadd(h,8,ud.Day)) EntranceTime, COALESCE(mx.maxExitInDay,Dateadd(h,20,ud.Day)) ExitDate
    FROM usersAndDates ud 
    LEFT JOIN minEntranceInDay me ON me.UserId = ud.UserId AND me.Day = ud.Day 
    LEFT JOIN maxExitInDay mx ON mx.UserId = ud.UserId AND mx.Day = ud.Day
)
SELECT *, DATEDIFF(h,EntranceTime,ExitTime)+":"+DATEDIFF(m,EntranceTime,ExitTime)+":"+DATEDIFF(s,EntranceTime,ExitTime) TimeHere
FROM GroupedData