SQL Query group by every month

Transact-SQL
1

I would like to generate the report for finance office to pay the salary for each staff.

I have some mass data in SQL server

STAFFCODE    TIME                TYPE
101       31/1/1900 09:00:00      IN
101       1/2/1900 03:02:00      OUT
102       31/1/1900 09:00:00      IN
102       31/1/1900 19:23:00     OUT

I would like to calculate each staff each month working minutes.

MONTH       STAFFCODE    WORKING_MINS
JAN/1900       101            900
JAN/1900       102            623
FEB/1900       101            182
2

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)

3

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