Can this be done as a SQL VIEW

Question

I have a SQL Server table of Customer's Events:

CREATE TABLE CustomerEvent
(
    CustomerID int,
    EventType int,
    EventDate datetime
)

There can be many EventTypes for the same customer in one day.

EventTypes are like

1 - CheckIn
2 - CheckOut
3 - ExamStart
4 - ExamEnd

Now I want to select Customers that are currently (today) on premises. That's Clients who had checked in, but hadn't checked out, no matter if they taking Exam currently or not. Can this be done as a SQL view, or do I have to write a stored procedure for that?

Answer 1

You want today. So I would suggest not exists:

select ce.customerid
from customerevent ce
where eventtype = 1 and
      event_date >= current_date and
      event_date < current_date + interval '1 day' and
      not exists (select 1
                  from customerevent ce2
                  where ce2.customerid = ce.customerid and
                        ce2.eventtype = 2 and
                        ce2.eventdate > ce.eventdate
                 );

You can easily incorporate this into a view.

Note: date/time functions are notoriously database specific, so the exact syntax for "today" may vary.

EDIT:

In SQL Server, this can be written as:

select ce.customerid
from customerevent ce
where eventtype = 1 and
      convert(date, event_date) >= concat(date, current_date) and
      not exists (select 1
                  from customerevent ce2
                  where ce2.customerid = ce.customerid and
                        ce2.eventtype = 2 and
                        ce2.eventdate > ce.eventdate
                 );