Count of employee present in last 12 hour

Transact-SQL
1

I have a table which record employee enter and exit detail as shown below.

I want to find out count of employee present last 12 hours that is how many employees of department A present at 13.00 , 12.00, 11.00 , ....1.00 hour.

Please guide.

Table
Table.jpg819×460 77.7 KB

2

for this solution, you will required a number table.

if you don't have one, you can replace it with a recursive cte query

; with 
-- number table
numbers as    
(
	select	n = 0
	union all
	select	n = n + 1
	from	numbers
	where	n	<= 99
),
-- group the entry - exit into a pair
cte1 as         
(
	select	*, 
		grp	= (row_number() over (partition by UserNo order by ActionTime) + 1) / 2
	from	@sample
),
-- convert the pair of 2 rows into one to obtain entry & exit time
cte2 as
(
	select	UserNo, Department, 
		entry_time = min(ActionTime),
		exit_time = max(ActionTime)
	from	cte1
	group by UserNo, Department, grp
),
-- here the numbers table comes into use
-- find the different of hours between the entry & exit time and use that limit the number of rows from numbers table
cte3 as
(
	select	*, hr = dateadd(hour, n, dateadd(minute, -datepart(minute, entry_time), entry_time))
	from	cte2 c
		inner join numbers n	on	n.n	<= datediff(hour, c.entry_time, c.exit_time)
)
-- the final result
select	Department, hr, count(*)
from	cte3
group by Department, hr
order by Department, hr
3 
SELECT Count(Distinct UserNo)  CountOfUserLast12Hrs
FROM YourTable 
WHERE [Action Time]>DateAdd(hh, -12, GetDate());