How to write a Tsql query to select the presence of a user in the workplace?

Question

SQL

COMPUTER ( Computer_id PK int not null, --Id Com
Name varchar(255) not null ) --Name Com
USER ( User_id PK int not null, -- user id Login varchar(255) not null , -- login users Name varchar (255) not null ) -- Name Users
S_EVENT ( Event_id PK int not null, --1,2,3,4 Name varchar(255) )-- Event Id 1 PC on 2 PC off 3 user in 4 user
EVENTS out ( Num int identity PK not null , -- Event number Computer_id , -- Inherited from Computer User_id ,-- Inherited from User Event_id,-- Inherited from Event date -- Today's date and time(Getdate()))
Task Generate query, where there will be 2 columns
1 This field is Name (Name of Employees)
2 Computer Status, where we compare
If the events for TODAY (tomorrow should no longer work) for this user (Event_id = 3) == (Event_id = 4), then "the user has gone away (lunches)"
If the events (Event_id = 3) > (Event_id = 4) for the user for all time, then "User is working" (it can work since yesterday)
If the last event (Event_id = 2) and (Event_id = 1) or User_id = null for the user, then "User is not present" (The value of the user is null in events 1 and 2 because he hasn't logged in yet)

Answer 1

select  u.[User_Id] , [Name] , [state] , [date] 
from [USER] u
join (
--соединим полученный результат с ФИО
select tt.User_Id , isnull([state], 2) [state], [date]
-- выбираем всех пользователей ,а значения нал заменим на 2
from
(
select e.User_Id ,min(Event_id) as [state], [date]
from EVENTS e
join
(
select max([Num]) num,Computer_Id
 from EVENTS
 group by Computer_Id
 ) t1 on 
 t1.num = e.Num
 where User_Id is not null
 group by  e.User_Id , [date]
 --Последние действие пользователя если был вход или выход


 union 
 ---------------------------------------Последнее действие на компьютере пользователя и компьютера 
select  User_Id , Ev_id , dt
from
( 
 select  num as num1, e.Computer_Id as CID, [USER_ID] as Us_id ,Event_Id as Ev_id , Date as dt
from EVENTS e
join
(
 select max(num) nam,Computer_Id
 from EVENTS
 group by Computer_Id
 ) t2 on 
 t2.nam = e.Num
  where User_Id is null

) as
   tnull
  inner join
  (

 select  e.Num , e.Computer_Id , e.User_Id , Event_Id , [Date]
from EVENTS e
join
(
  select max(num) num,User_Id 
  from EVENTS
  where User_Id is not null
  group by User_Id 
 
  ) muser on 
  muser.num = e.Num
 --order by Computer_Id

  ) as euser on
  euser.Computer_Id = tnull.CID
--Последние дествие на компьютере выкл/вкл ПК и пред действие пользователя на этом ПК(выход) ,но выводим только Пользователя и текущий статус ПК
) tt1

-----------------------------------------------------
right join

(
Select u.[User_Id]  
 from [USER] u) tt on 
 tt.User_Id = tt1.User_Id
--выявляем оставшихся пользователей (те кто не попали будут со значением NULL)
) t1 on t1.User_Id = u.User_Id

Answer 2

Выбрать пользователь, состояние из событий 
где момент = ( выбрать последний момент из событий для пользователя )