CODEWITHSUNDEEP
sqlms-access
ayha
ayha

Reputation: 181

Finding time difference

I am trying to execute the following query in MS Access to find difference between two times. The time format is, eg, start time 8:00 and end time 14:00. But I get error in the query, please help me to fix it.

PARAMETERS [Enter Date] DateTime;

SELECT
    M.CompanyID AS Emp_ID,
    MIN(H.EventTime) AS Attending,
    MAX(H.EventTime) AS Leaving,
    Format( (Leaving-Attending), "hh:mm:ss") AS TotalHrs
FROM
    Member AS M
    LEFT JOIN History AS H ON M.UserID = H.EventUserID 
    AND
    ( Format( [EventDate], "dd/mm/yyyy" ) ) = [Enter Date]    
WHERE
    companyId NOT IN (select companyid from exceptions)
GROUP BY
    M.CompanyID, EventDate
ORDER BY
    CompanyId;

Ayha

Upvotes: 3

Views: 184

Answers (2)

HansUp
HansUp

Reputation: 97101

You can move most of that SELECT into a subquery. Then from the parent query you can use the names defined in the subquery, Leaving and Attending, when you compute their difference.

PARAMETERS [Enter Date] DateTime;
SELECT
    sub.Emp_ID,
    sub.Attending,
    sub.Leaving,
    Format((sub.Leaving - sub.Attending), "hh:mm:ss") AS TotalHrs
FROM
    (
        SELECT
            M.CompanyID AS Emp_ID,
            MIN(H.EventTime) AS Attending,
            MAX(H.EventTime) AS Leaving
        FROM
            Member AS M
            LEFT JOIN History AS H
            ON
                M.UserID = H.EventUserID 
            AND Format([EventDate], "dd/mm/yyyy") = [Enter Date]    
        WHERE
            M.companyId NOT IN (select companyid from exceptions)
        GROUP BY
            M.CompanyID, EventDate
    ) AS sub
ORDER BY sub.Emp_ID

Most of the heavy lifting is done within the subquery. The parent query simply retrieves subquery values and subtracts a pair of values, so this suggestion should not impose a significant additional performance penalty.

Upvotes: 3

Johnny Bones
Johnny Bones

Reputation: 8404

Leaving and Attending do not have values until the query has been completed. Therefore, Leaving-Attending means nothing to Jet while it's processing the SELECT part of a query. You will need to substitute MIN(H.EventTime) for Attending and MAX(H.EventTime) for Leaving in the calculation for TotalHrs.

Upvotes: 2

Related Questions