Is there any other way of solving the SQL question involving employee data?

Question

A company record its employees movement In and Out of office in a table with 3 columns (Employee id, Action (In/Out), Created).

First entry for each employee is “In”. Every “In” is succeeded by an “Out". No data gaps and, employee can work across days.

Employee id  Action  Created
1             In    2019-04-01 12:00:00
1             Out   2019-04-01 15:00:00
1             In    2019-04-01 17:00:00
1             Out   2019-04-01 21:00:00

1.) Find number of employees inside the Office at current time. Below is my attempt.

Select sum(case when Action = 'IN' then 1 when Action = 'OUT' then -1 end) from Company where Created = getdate()

One question per *question*. Not three. I'm voting to close. — Gordon Linoff, Sep 12 '20 at 12:22
Try to change that last `=` to `<` that should work... or maybe you could completely remove your `where Created = getdate()` — garzj, Sep 12 '20 at 12:48
Are you trying to find discrepancies ? Otherwise as you say `no gaps` checking for today should be enough by `cast(created as date) = cast(getdate() as date)` because you cannot check equality for timestamp column for this. — Sujitmohanty30, Sep 12 '20 at 12:59
First step is to find the set of "most recent rows" for each employee ("most recent" is the row where Created <= [your target date and time]). Now how does one do that? row_number() is often used. — SMor, Sep 12 '20 at 13:14
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. — philipxy, Sep 12 '20 at 18:07

Somy · Answer 1 · 2020-09-12T12:51:41.960

0

Assuming no gaps.

Select sum(case when Action = 'IN' then 1 when Action = 'OUT' then -1 end) from Company where Created <= getdate();

Or, below without any where clause. The number would be true as of that moment in point of time -

Select sum(case when Action = 'IN' then 1 when Action = 'OUT' then -1 end) from Company

edited Sep 12 '20 at 12:51

answered Sep 12 '20 at 12:45

Somy

1,474
1
4
13

score 0 · Answer 2 · answered Sep 12 '20 at 13:25

If the employee is in the building then their latest [Action] is set to 'In' and there is not a corresponding 'Out' action. Something like this

with entry_cte as (
    select *, row_number() over (partition by employee_id order by created desc) rn)
select count(*) in_building_count
from entry_cte ec
where [action]='In'
      and rn=1;

Is there any other way of solving the SQL question involving employee data?

2 Answers2