try this!
SET DATEFORMAT dmY;
if object_id('tempdb..#tmpinout') is not null
drop table #tmpinout
-- build temp table with data
select '101' as STAFFCODE, cast('31/1/2015 09:00:00' as varchar(100)) as Time,cast('IN' as varchar(3)) as type
into #tmpinout
insert into #tmpinout values ('101','31/1/2015 19:02:00','OUT')
insert into #tmpinout values ('102','31/1/2015 09:00:00','IN')
insert into #tmpinout values ('102','31/1/2015 19:23:00','OUT')
-- get row number by datetime and staff member to get rows in correct order
;
with inout as
(
select row_number() over (partition by staffcode order by cast(time as datetime)) as rowp,staffcode,cast(time as datetime) as inoutdate,type
from #tmpinout
)
-- join to next record, if no clock out, then no result for that day.
select staffcode,year(indate) as Year,month(indate) as Month,sum(datediff(mi,indate,outdate)) as Minutes
from
(
select i.staffcode,i.inoutdate as indate,o.inoutdate as outdate
from inout i
join inout o on o.rowp = i.rowp + 1 and i.staffcode = o.staffcode
) a
group by staffcode,year(indate),month(indate)
If you expect the IN/OUT rows to always, you can use a very easy method. If you could have missing IN and/or OUT, you would need to add more complex logic to see which IN(s) or OUT(s) to exclude:
SELECT
DATEADD(MONTH, DATEDIFF(MONTH, '20000101', time), '20000101') AS Month,
Staffcode,
(SUM(CASE WHEN type = 'OUT' THEN DATEDIFF(SECOND, '20000101', Time) ELSE 0 END) -
SUM(CASE WHEN type = 'IN' THEN DATEDIFF(SECOND, '20000101', Time) ELSE 0 END)) / 60.0 AS Working_Mins,
CASE WHEN SUM(CASE WHEN type = 'OUT' THEN 1 ELSE 0 END) <> SUM(CASE WHEN type = 'IN' THEN 1 ELSE 0 END)
THEN '!!ERROR -- Mismatched IN/OUT entries, do NOT rely on minutes reported for this Staffcode for this Month!!'
ELSE '' END AS Message
FROM #tmpinout
GROUP BY
DATEADD(MONTH, DATEDIFF(MONTH, '20000101', time), '20000101'),
Staffcode