May 24, 2015 at 10:40 am
Dear Experts,
How to combile multiple rows to single rows for the below sql query.
SELECT dbo.AccessLog.RCDID, dbo.AccessLog.EMPLOYEEID, dbo.AccessLog.LOGDATE, LEFT(dbo.AccessLog.LOGTIME, 5) AS LOGTIME,
dbo.AccessLog.INOUT
FROM dbo.AccessLog LEFT OUTER JOIN
dbo.LogType ON dbo.AccessLog.INOUT = dbo.LogType.INOUT LEFT OUTER JOIN
dbo.viwEmployee ON dbo.AccessLog.EMPLOYEEID = dbo.viwEmployee.Employee_ID
WHERE dbo.AccessLog.EMPLOYEEID='10763' AND (dbo.AccessLog.LOGDATE BETWEEN '01/04/2015' AND '01/04/2015')
ORDER BY dbo.AccessLog.EMPLOYEEID
The reult for the above query is:
RCDID | EmployeeID | LOGDATE | LOGTIME | INOUT
1 10763 01/04/2015 08:00 0
1 10763 01/04/2015 19:46 1
I need the result like the below
RCDID | EmployeeID | LOGDATE | IN | OUT
1 10763 01/04/2015 08:00 19:46
Thanks.
May 24, 2015 at 11:25 am
The following should get you what you're looking for...
SELECT
al1.RCDID,
al1.EMPLOYEEID,
al1.LOGDATE,
LEFT(al1.LOGTIME, 5) AS [IN],
LEFT(al3.LOGTIME, 5) AS [OUT]
FROM
dbo.AccessLog al1
CROSS APPLY (
SELECT TOP 1
al2.LOGTIME
FROM
dbo.AccessLog al2
WHERE
al1.RCDID = al2.RCDID
AND al1.EmployeeID = al2.EmployeeID
AND al1.LOGDATE = al2.LOGDATE
AND al1.LOGTIME < al2.LOGTIME
AND al2.INOUT = 1
ORDER BY
al2.LOGTIME ASC
) al3
WHERE
al1.EMPLOYEEID = '10763'
AND al1.INOUT = 0
AND al1.LOGDATE >= '01/04/2015'
AND al1.LOGDATE < '01/05/2015'
ORDER BY
al1.EMPLOYEEID
Before you proceed, however, I would recommend that you rethink the table design for dbo.AccessLog. Splitting the Log date/time into two separate columns is bad idea. What happens when when a shift starts in the evening of 1 day and doesn't end until the next day?
May 24, 2015 at 11:22 pm
Exactly....
Thanks
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply