Click here to Skip to main content
15,942,757 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Im using Ms access database and my table structure looks like,

<br />
EmpID   InDate             InTime         Type<br />
---------------------------------------------<br />
25      03-Sep-12   9:35:35 AM  IN<br />
25  03-Sep-12   10:31:32 AM OUT<br />
25  03-Sep-12   10:34:13 AM IN<br />
25  03-Sep-12   11:05:08 AM OUT<br />
25  03-Sep-12   11:08:39 AM IN<br />
25  03-Sep-12   12:13:28 PM OUT<br />
25  03-Sep-12   12:18:56 PM IN<br />
25  03-Sep-12   1:44:50 PM  OUT<br />
25  03-Sep-12   1:51:06 PM  IN<br />
25  03-Sep-12   2:01:15 PM  OUT<br />
25  03-Sep-12   2:21:15 PM  IN<br />
25  03-Sep-12   2:22:10 PM  OUT<br />


Another table called emp which contains the list of employees.

The expected output is,
<br />
EmpID  Name   Duration   outduration<br />
----------------------------------------<br />
   25   xyz    4:08:39     0:17:56<br />

I use below query,

SQL
SELECT
    Sub.EmpID,sub.firstname + sub.lastname as Name,
     (24*Int(Sum(sub.end_time - sub.start_time))+Format(Sum(sub.end_time - sub.start_time),'h')) & Format(Sum(sub.end_time - sub.start_time),':nn:ss') AS duration,
     (24*Int(Sum(sub1.outend_time - sub1.outstart_time))+Format(Sum(sub1.outend_time - sub1.outstart_time),'h')) & Format(Sum(sub1.outend_time - sub1.outstart_time),':nn:ss') AS outduration
FROM
    (
        SELECT
            o.SNo,
            o.InTime AS end_time,
            o.EmpID,
            o.Type,
            o.InDate,
            (
                SELECT TOP 1 i.InTime
                FROM EmpLog AS i
                WHERE
                        i.Type = 'IN'
                    AND i.EmpID = o.EmpID
                    AND i.InTime < o.InTime
                   and i.InDate=o.InDate
                ORDER BY i.InDate, i.InTime DESC
            ) AS start_time,e.firstname,e.lastname
        FROM EmpLog AS o,emp as e WHERE o.Type='OUT'  and e.emp_ID=o.EmpID and o.InDate>=#9/3/2012# and o.InDate<=#9/3/2012#
    ) AS sub,

(select
      ot.InTime as outend_time,
       (
         SELECT TOP 1 it.InTime
        FROM EmpLog AS it
              WHERE it.Type = 'OUT' AND it.EmpID = ot.EmpID AND it.InTime < ot.InTime and it.InDate=ot.InDate ORDER BY it.InDate, it.InTime DESC
            ) AS outstart_time
          From EmpLog as  ot, emp as ep where   ot.Type='IN'  and ep.emp_ID=ot.EmpID and ot.InDate>=#9/3/2012# and ot.InDate<=#9/3/2012#   and
          Format(ot.InTime - (SELECT TOP 1 it.InTime
                FROM EmpLog AS it
                WHERE
                        it.Type = 'OUT'
                    AND it.EmpID = ot.EmpID
                    AND it.InTime < ot.InTime and it.InDate=ot.InDate
                ORDER BY it.InDate, it.InTime DESC), 'hh:nn:ss')<="00:10:00")  as sub1

 group by sub.EmpID,sub.lastname,sub.firstname order by sub.firstname asc;


But the output of the query,
<br />
EmpID  Name   Duration   outduration<br />
----------------------------------------<br />
   25   xyz    20:43:15     0:17:56  


If i execute sub and sub2 as seperate queries. It gives correct answer. But if i group them into 1 the inner query that is sub.duration is wrong. Can any 1 suggest me where is the problem.
Posted
Updated 21-Sep-12 0:25am
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900