S K S K - 4 years ago 230
SQL Question

MS Access: Summing time greater than 24 hours in query restarts from 1 again

I have a table trans_hist in MS Access program where time is stored in short Date format "HH:MM" ex:

[![Image][1]][1]

Now I have created a query which tells total time for each user (Just simply summing the time) but if the total time exceeds 24 hours then it resets and starts over again from 00:15(all time are stored in 15 minutes intervals)

Problem: For Customer(UID) 1 the total time should have been 32:30 however it shows 8:30

Current Result:

UID Time_Elapsed
1 5:00
1 8:30
1 9:00
1 6:00
2 2:15
2 2:00
3 1:15
5 4:00
1 4:00


Result:

[![Image][2]][2]


  • DATA



Cust_UID Trans_Date Agen_Name Prog_Name Prime_Serv Prime_Serv_Time
10014 13-Dec-15 LAC RA BMC 01:00
10021 14-Dec-15 LAC RA AP 01:00
10022 15-Dec-15 LAC RA AP 01:00
10021 16-Dec-15 LAC RA SM 00:45
10020 17-Dec-15 LAC RA AP 01:00
10027 18-Dec-15 LAC RA DA 00:15
10028 18-Dec-15 LAC RA DA 00:15
10026 18-Dec-15 LAC RA DA 00:15
10029 18-Dec-15 LAC RA DA 00:15
10030 18-Dec-15 LAC RA DA 00:15
10031 18-Dec-15 LAC RA DA 00:15
10023 19-Dec-15 LAC RA Clinical 02:00
10023 20-Dec-15 LAC RA Clinical 01:30
10023 20-Dec-15 LAC RA Clinical 02:00
10020 21-Dec-15 LAC RA SM 00:15
10023 21-Dec-15 LAC RA SM 00:30
10022 22-Dec-15 LAC RA Clinical 00:30
10022 22-Dec-15 LAC RA IB 00:30
10021 22-Dec-15 LAC RA IB 00:30
10009 22-Dec-15 LAC RA IB 00:30
10019 23-Dec-15 LAC RA STM 00:45
10009 23-Dec-15 LAC RA Staff - In 00:30
10021 23-Dec-15 LAC RA Staff - In 00:30
10022 23-Dec-15 LAC RA Staff - In 00:30
10024 23-Dec-15 LAC RA Staff - In 00:30
10033 23-Dec-15 LAC RA Staff - In 00:30
10025 23-Dec-15 LAC RA Clinical 00:45
10035 28-Dec-15 LAC OA CA 05:00
10040 28-Dec-15 LAC OA CA 05:00
10039 28-Dec-15 LAC OA CA 05:00
10038 28-Dec-15 LAC OA CA 05:00
10042 28-Dec-15 LAC OA CA 05:00
10036 28-Dec-15 LAC OA CA 05:00
10037 28-Dec-15 LAC OA CA 05:00
10006 30-Dec-15 LAC Test 1 DA 01:45
10005 30-Dec-15 LAC Test 2 DG 01:45
10015 30-Dec-15 LAC Test 2 IB 02:15
10015 30-Dec-15 LAC Test 4 DG 03:15
10019 30-Dec-15 LAC OA CA 15:30
10005 31-Dec-15 LAC OA CA 12:00

[Data][3]

Result

Prog_Name Prime_Serv Total_Serv_Time
OA CA 62:30
RA AP 3:0
RA BMC 1:0
RA Clinical 7:45
RA DA 2:30
RA IB 2:30
RA Staff - In 2:30
RA SM 2:30
RA STM 1:45
Test 1 DA 2:45
Test 2 DG 2:45
Test 2 IB 2:15
Test 4 DG 3:15

[Result][4]

Answer Source

I hope, this SQL query helps:

SELECT T.UID,
       CSTR(total_hours 
                + INT(total_min / 60)) 
                + ":" 
                + CSTR(total_min Mod 60) as result
FROM
    (SELECT UID,
            SUM(HOUR(TH.time_elapsed)) AS total_hours,
            SUM(MINUTE(TH.time_elapsed)) AS total_min
    FROM trans_hist AS TH
    GROUP BY UID) AS T
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download