CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

How to select InTime and OutTime using SQL query

I have a table named tblEventRegister(Tagvalue int, EventDate datetime, TagName nvarchar(50), EventName nvarchar(50)).

The table has the following data:

EventDate                     TagName  EventName    TagValue       
2021-03-18 12:06:35.547        A1        E1           1    
2021-03-18 12:06:37.547        A2        E1           1       
2021-03-18 12:06:38.547        A3        E1           1         
2021-03-18 12:06:45.547        A1        E1           0     
2021-03-18 12:06:47.547        A1        E1           0 
2021-03-18 12:06:48.547        A1        E1           1 
2021-03-18 12:06:55.547        A1        E1           0 
2021-03-18 12:06:56.547        A1        E1           0 
2021-03-18 12:06:57.547        A1        E1           1

When TagValue=1 then EventDate is InTime and when tagvalue is 0 then EventDate is OutTime. I used the following query to find the InTime and OutTime.

select EventDate, TagName, EventName, Tagvalue
    , (case Tagvalue when 1 then EventDate end) intime
    , (case Tagvalue when 0 then EventDate end) outtime
from tblEventRegister with (nolock)

The result coming is

 EventDate               TagName  EventName TagValue       InTime              OutTime  
2021-03-18 12:06:35.547   A1        E1        1    2021-03-18 12:06:35.547      Null
2021-03-18 12:06:37.547   A2        E1        1    2021-03-18 12:06:37.547      Null
2021-03-18 12:06:38.547   A3        E1        1    2021-03-18 12:06:38.547      Null
2021-03-18 12:06:45.547   A1        E1        0            Null               2021-03-18 12:06:45.547
2021-03-18 12:06:47.547   A2        E1        0            Null               2021-03-18 12:06:47.547
2021-03-18 12:06:48.547   A1        E1        1    2021-03-18 12:06:48.547      Null
2021-03-18 12:06:55.547   A3        E1        0            Null               2021-03-18 12:06:55.547
2021-03-18 12:06:56.547   A1        E1        0            Null               2021-03-18 12:06:56.547
2021-03-18 12:06:57.547   A1        E1        1    2021-03-18 12:06:56.547      Null

But the result should be

EventDate               TagName  EventName TagValue       InTime               OutTime  
2021-03-18 12:06:35.547   A1        E1        1    2021-03-18 12:06:35.547    2021-03-18 12:06:45.547
2021-03-18 12:06:37.547   A2        E1        1    2021-03-18 12:06:37.547    2021-03-18 12:06:47.547
2021-03-18 12:06:38.547   A3        E1        1    2021-03-18 12:06:38.547    2021-03-18 12:06:55.547   
2021-03-18 12:06:48.547   A1        E1        1    2021-03-18 12:06:48.547    2021-03-18 12:06:56.547
2021-03-18 12:06:57.547   A1        E1        1    2021-03-18 12:06:57.547      Null

How can I achieve this?

Upvotes: 0

Views: 375

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270623

If I assume that each "in" is followed by at most one corresponding "out" -- that is, there are no "in"s followed by "in" or "out"s followed by "out", then this problem is not difficult. It becomes much harder with dirtier data.

One approach is simply to enumerate the rows by "in"s and "out"s and then aggregate:

select tagname, eventname, max(tagvalue), min(eventdate) as intime,
       nullif(max(eventdate), min(eventdate)) as outtime
from (select er.*,
             row_number() over (partition by tagname, eventname, tagvalue order by eventdate) as seqnum
      from tblEventRegister er
     ) er
group by tagname, eventname, seqnum
order by intime;

The purpose of the nullif() is to handle the case where there is no following "out".

Here is a db<>fiddle.

Upvotes: 0

Dale K
Dale K

Reputation: 27284

The following returns your desired results... note though, it doesn't take into account any other possible data issues. So it assumes that there will be a matching out time for every in time, or not out time. But it won't handle multiple in times or out times.

How it works is by allocating a row number to each TagName, TagValue pair and then joins them.

declare @tblEventRegister table (Tagvalue int, EventDate datetime, TagName nvarchar(50), EventName nvarchar(50));

insert into @tblEventRegister (EventDate, TagName, EventName, TagValue)
values
('2021-03-18 12:06:35.547', 'A1', 'E1', 1),
('2021-03-18 12:06:37.547', 'A2', 'E1', 1),
('2021-03-18 12:06:38.547', 'A3', 'E1', 1),
('2021-03-18 12:06:45.547', 'A1', 'E1', 0),
('2021-03-18 12:06:47.547', 'A2', 'E1', 0),
('2021-03-18 12:06:48.547', 'A1', 'E1', 1),
('2021-03-18 12:06:55.547', 'A3', 'E1', 0),
('2021-03-18 12:06:56.547', 'A1', 'E1', 0),
('2021-03-18 12:06:56.547', 'A1', 'E1', 1);

with cte as (
    select *
        , row_number() over (partition by TagName, TagValue order by EventDate) rn
    from @tblEventRegister
)
select C1.EventDate, C1.TagName, C1.EventName, C1.TagValue, C1.Eventdate, C2.EventDate
from cte C1
left join cte C2 on C2.TagName = C1.TagName and C2.TagValue = 0 and C1.rn = C2.rn
where C1.TagValue = 1
order by C1.EventDate asc;

Returns:

EventDate TagName EventName TagValue Eventdate EventDate
2021-03-18 12:06:35.547 A1 E1 1 2021-03-18 12:06:35.547 2021-03-18 12:06:45.547
2021-03-18 12:06:37.547 A2 E1 1 2021-03-18 12:06:37.547 2021-03-18 12:06:47.547
2021-03-18 12:06:38.547 A3 E1 1 2021-03-18 12:06:38.547 2021-03-18 12:06:55.547
2021-03-18 12:06:48.547 A1 E1 1 2021-03-18 12:06:48.547 2021-03-18 12:06:56.547
2021-03-18 12:06:56.547 A1 E1 1 2021-03-18 12:06:56.547 NULL

Note 1: This shows how to present your sample data as DDL+DML - I encourage you to include this in future for a faster response.

Note 2: Do not use with (nolock) as some kind of default query setting as the results could be unexpected. You should only use that hint if you completely understand the consequences and you absolutely have to.

Upvotes: 2

Related Questions