CODEWITHSUNDEEP
sqlsql-server
codeSeven
codeSeven

Reputation: 469

SQL - time in / out in single query

Employee_tb

  sqnc_no userid  name  branch    txn_date      task

  1         1       Joe   CA        2020-04-06   sampletask
  2         1       Joe   CA        2020-04-07   sampletask1

Branch_tb

branchcode   branchdesc
CA            SampleBranch

Time_Tb

Userid  Txn_Date                    Status
1       2020-04-06 08:25:23.567     IN
1       2020-04-06 18:15:42.493     OUT
1       2020-04-07 08:25:23.567     IN
1       2020-04-07 18:15:42.493     OUT

I have here a sample table which employee has a time in and out, I want to get the time in and out of the employee in one query, can someone help me how to do it? thank you in advance.

Result I want to display:

userid   name   branch         txn_date     in/out
1        joe    SampleBranch   2020-04-06   08:25 AM / 6:15PM
1        joe    SampleBranch   2020-04-07   08:25 AM / 6:15PM

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

One method is to assign groups of "in"s by using a cumulative sum and then aggregating:

select t.id, convert(date, txn_date),
       convert(time, min(txn_date)) as in_time,
       convert(time, max(case when status = 'out' then txn_date end)) as out_time
from (select t.*,
             sum(case when status = 'in' then 1 else 0 end) over (partition by userid, convert(date, txn_date) order by txn_date) as grp
      from t
     ) t 
group by t.id, convert(date, txn_date), grp;

It is a simple matter to join to the other table to get the name of the employee.

Upvotes: 3

Related Questions