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.
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.
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
SELECT Count(Distinct UserNo) CountOfUserLast12Hrs
FROM YourTable
WHERE [Action Time]>DateAdd(hh, -12, GetDate());