Well, I gave it a go. I used the sample data in the post to create two functions to convert time to seconds and the other way around to convert seconds back to time. I then used a GROUP BY to aggregate the data on CardNo and IODate. With the sample data I was able to create a query that returned a decent result.

Unfortunately I used a couple of assumptions to calculate the 'InOffice' value, namely that every IN scan has one OUT scan and that both the IN and OUT scan occur the same day (so not usable in an 24 hour time registration situation). After importing your data into a temp table I discovered that my assumptions were wrong. Just one example IODate 2012-04-03, CardNo 100 has numerous IN scans without an OUT scan. There are numerous other 'Data annomalies'. Because of this the 'InOffice' value will be wrong in my query.

I therefore don't think it is possible, in any easy way, to get the result you want without first improving the data quality. Below is the code that worked for the sample data. Perhaps you, or somebody else, can use it to find a solution. I will be following the thread to see what sharper minds then my own can make of it.

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