How I sum or aggregate time data in SQL server, from cast
00:34:32
00:17:15
--------------------
TOTAL = 01:26:19.0000000
should : 00:51:47.0000000
What I have tried:
SELECT DATEADD(hh, t.TotalHours, DATEADD(n, t.TotalMinutes, DATEADD(ss, t.TotalSeconds, CAST('00:00:00' AS time)))) AS TotalTime
FROM (
SELECT SUM(DATEPART(hh, convert(varchar, CAST((tsp.wa1 - (ta.regDate+ta.regTime) ) as time(0)), 14))) AS TotalHours,
SUM(DATEPART(n, convert(varchar, CAST((tsp.wa1 - (ta.regDate+ta.regTime) ) as time(0)), 14))) AS TotalMinutes,
SUM(DATEPART(ss, convert(varchar, CAST((tsp.wa1 - (ta.regDate+ta.regTime) ) as time(0)), 14))) AS TotalSeconds
FROM tbantrian ta WITH (NOLOCK)
LEFT JOIN tbrawatjalan trj WITH ( NOLOCK ) ON ta.struckNo = trj.struk
LEFT JOIN tbcustomer tc WITH ( NOLOCK ) ON ta.custCode = tc.custCode
LEFT JOIN tbdoctor td WITH ( NOLOCK ) ON ta.doctor = td.docCode
LEFT JOIN tbSoapPoli tsp WITH ( NOLOCK ) ON trj.tranCode = tsp.tranCode
LEFT JOIN tbSoapPoliPerawat tspp WITH ( NOLOCK ) ON trj.tranCode = tspp.tranCode
LEFT JOIN tbResepDokter trd WITH ( NOLOCK ) ON trj.tranCode = trd.tranCode
LEFT JOIN tbpoli tp WITH ( NOLOCK ) ON ta.poly = tp.polyCode
LEFT JOIN tbuser tu WITH ( NOLOCK ) ON ta.doctor = tu.docCode
Where ta.regDate >= '2024-07-11 00:00:00' and ta.regDate <= '2024-07-11 01:59:59'
and ta.status <> 'Batal'and ta.status <> 'Antri' and ta.status <> 'Proses' and ta.status <> 'Antri'
and tp.polyCode = 'POL043'
and tsp.ketPasien ='Esklusi'
) AS t;