SELECT 
	StIn.CardNo 
	,StIn.IODate 
	,dbo.fnSecondsToTime(DateDiff(ss,MIN(StIn.IOTime), MAX(StOut.IOTime))) as TotalHours 
	,dbo.fnSecondsToTime(SUM(dbo.fnTimeToSeconds(StOut.IOTime)) - SUM(dbo.fnTimeToSeconds(StIn.IOTime))) as  Inoffice
FROM 
	stagingINOUT StIn, 
	stagingINOUT StOut
WHERE 
	StIn.IOTypeN = 12
	AND StOut.IOTypeN = 11
	AND StIn.CardNo = StOut.CardNo
GROUP BY 
	StIn.CardNo, 
	StIn.IODate
ORDER BY 
	CardNo
	,IODate

CREATE FUNCTION fnSecondsToTime (@TotalSeconds int)
RETURNS varchar(8)
AS
BEGIN 
  RETURN (SELECT CONVERT(char(8), DATEADD(second, convert(int,@TotalSeconds), '19000101'), 108))
END 
GO

CREATE FUNCTION fnTimeToSeconds (@TimeToConvert time)
RETURNS int
AS
BEGIN 
  RETURN (SELECT (DATEPART(hour, @TimeToConvert) * 3600) +(DATEPART(minute, @TimeToConvert) * 60) + DATEPART(second, @TimeToConvert))
END 
GO