Form a MS SQL query to select the minimum event and subtract a certain time from it?

Question

SQL

How to write a Tsql query to select the presence of a user in the workplace?
Here you can take the basis of the database
And here is its Backup Here is the fidle Task:

1. I want to make a request to find events for each user for a month (or a period of time)
   
2. Then take the minimum event for each day (if for a month, then all events)
   
3. Next, take the maximum event event for each day
   
4. Count the number of inputs/outputs
   
5. If the number of inputs > outputs, then subtract 21:00 when the current time is reached (DATETIMEFROMPARTS)
   
6. If the number of inputs < outputs , then subtract 21:00 when the current time is reached
   
7. If the number of inputs = outputs, then subtract the output - input
   

I have already resolved the issue with the request for the period of this month

SELECT *
from [EVENTS] 
WHERE month([date]) = month(getdate()) and year([date]) = year(getdate())

If you need a gap, then BETWEEN can be used
Question, how to count the number of Event_Id = 3 and Event_Id = 4 (entry and exit events), and then compare and display for a month?

Answer 1

normally))

SELECT 
 COUNT(*) AS_all_events,  
 SUM(CASE WHEN Event_Id = 3 THEN 1 ELSE 0 END) as _enter_events,
 SUM(CASE WHEN Event_Id = 4 THEN 1 ELSE 0 END) as _exit_events
FROM ...