Below is my query which is giving MIN(InTime) & MAX(OutTime) for one day of one employee. Now I want to show total effective hours of employee by calculating all the In & Out timings difference of employee for one day.

Any help on this will be appreciated.

WITH cte_tables (zcardno, zdate, zs_datetime, zchannel_no) AS

( SELECT cardno, DATEADD(dd, DATEDIFF(dd, 0, s_datetime), 0), s_datetime, [channel no] as action

FROM transactions where s_datetime >= '2013-03-01'

UNION ALL SELECT cardno, DATEADD(dd, DATEDIFF(dd, 0, s_datetime), 0), s_datetime, Action

FROM custom_transactions where s_datetime >= '2013-03-01'),

cte_minmax (acardno, adate, aMinS_datetime, aMaxS_datetime) AS

(SELECT zcardno, zdate, MIN(zs_datetime), NULL FROM cte_tables

WHERE zchannel_no = 2 GROUP BY zcardno, zdate UNION ALL SELECT zcardno, zdate, NULL, MAX(zs_datetime)

FROM cte_tables WHERE zchannel_no = 1 GROUP BY zcardno, zdate)

SELECT m.name, c.acardno AS cardno, REPLACE(CONVERT(VarChar(50), c.adate, 103),'/','-') AS [date],

LEFT(CONVERT(varchar,MAX(c.aMinS_datetime),108),5) AS InTime,

LEFT(CONVERT(varchar,MAX(c.aMaxS_datetime),108),5) AS OutTime FROM cte_minmax c

inner join master m on c.acardno = m.cardno where acardno in ('14109393') and adate between '2013-06-01' and '2013-06-08'

GROUP BY m.name, c.acardno, c.adate order by c.acardno