none
Select valid In Out data only - Get only First In and First Out data RRS feed

  • Question

  • create table #temp (ID varchar(50),DateTime_ Time,Type varchar(10))

    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 08:07:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 08:15:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 10:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 11:07:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 16:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 16:37:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 08:07:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 08:17:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 14:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 15:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 15:10:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 17:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 19:07:24.000','O')

    select * from #temp
    drop table #temp

    Get only First In and First Out Only

    Result : 
    ID     Time                                                 Type
    1001   2020-01-01 08:07:24.000      I
    1001   2020-01-01 10:07:24.000                   O
    1001   2020-01-01 11:07:24.000      1
    1001   2020-01-01 16:07:24.000    O
    1002   2020-01-01 08:07:24.000    1
    1002   2020-01-01 14:07:24.000      O
    1002   2020-01-01 15:10:24.000      1
    1002   2020-01-01 17:07:24.000      O

    • Edited by powsul Friday, January 17, 2020 1:00 PM
    Friday, January 17, 2020 12:46 PM

All replies

  • create table #temp (ID varchar(50),DateTime_ DateTime,Type varchar(10))
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 08:07:24.000','I')
    ,('1001','2020-01-01 08:15:24.000','I')
    ,('1001','2020-01-01 10:07:24.000','O')
    ,('1001','2020-01-01 11:07:24.000','I')
    ,('1001','2020-01-01 16:07:24.000','O')
    ,('1001','2020-01-01 16:37:24.000','O')
    ,('1002','2020-01-01 08:07:24.000','I')
    ,('1002','2020-01-01 08:17:24.000','I')
    ,('1002','2020-01-01 14:07:24.000','O')
    ,('1002','2020-01-01 15:07:24.000','O')
    ,('1002','2020-01-01 15:10:24.000','I')
    ,('1002','2020-01-01 17:07:24.000','O')
    ,('1002','2020-01-01 19:07:24.000','O');
    
    ;with mycte as 
    (select *
    ,
    row_number() Over( partition by ID order by DateTime_)
    -row_number() Over( partition by ID,Type order by DateTime_)
    grp
    from #temp
    )
    ,mycte2 as (
    select * ,
    row_number() Over( partition by ID,Type, grp order by DateTime_) rn
    from mycte
    )
    select ID,DateTime_,Type 
    from mycte2
    where rn=1
    Order by ID,Datetime_
    
    
    drop table #temp

    Friday, January 17, 2020 2:07 PM
    Moderator
  • --or

    ;with mycte as (select * 
    ,lag(type)Over(order by id, DateTime_) grp
    from #temp)
    
    select ID,DateTime_,Type 
    from mycte
    where Type<> grp or grp is null

    Friday, January 17, 2020 5:19 PM
    Moderator
  • SELECT ID, Datetime_,type FROM ( SELECT ID, Datetime_,type ,ROW_NUMBER() OVER (PARTITION BY ID, grp ,Type ORDER BY Datetime_) AS Seq FROM #temp t0 cross APPLY (SELECT MIN(Datetime_) AS grp FROM #temp t WHERE ID=t0.ID and Datetime_ >= t0.Datetime_ AND type <> t0.type )t1 )t WHERE Seq = 1

    order by ID, Datetime_



    Friday, January 17, 2020 7:23 PM
    Moderator
  • Thanks 

    one more question  the same data get First in Last Out data only

    Saturday, January 18, 2020 5:00 AM
  • thanks

    Saturday, January 18, 2020 5:30 AM
  • thanks
    Saturday, January 18, 2020 5:30 AM
  • Hi powsul, 

    If you would like to get First in Last Out data , Please try following script . 

    By the way , pease kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    create table #temp (ID varchar(50),DateTime_ Time,Type varchar(10))
    
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 08:07:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 08:15:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 10:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 11:07:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 16:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 16:37:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 08:07:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 08:17:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 14:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 15:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 15:10:24.000','I')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 17:07:24.000','O')
    insert into #temp (ID,DateTime_,Type) values ('1002','2020-01-01 19:07:24.000','O')
    
    ;with mycte as (select * 
    ,lag(type)Over(order by id, DateTime_) lg
    ,lead(type)Over(order by id, DateTime_) ld
    from #temp)
    
    select ID,DateTime_,Type 
    from mycte
    where Type='I' and (lg='O' or lg is null)
    or Type='O' and (ld='I' or ld is null)
    
    drop table #temp
    
    /*
    ID                                                 DateTime_        Type
    -------------------------------------------------- ---------------- ----------
    1001                                               08:07:24.0000000 I
    1001                                               10:07:24.0000000 O
    1001                                               11:07:24.0000000 I
    1001                                               16:37:24.0000000 O
    1002                                               08:07:24.0000000 I
    1002                                               15:07:24.0000000 O
    1002                                               15:10:24.0000000 I
    1002                                               19:07:24.0000000 O
    */
    

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 20, 2020 6:44 AM
  • Thanks 

    one more question  the same data get First in Last Out data only

    create table #temp (ID varchar(50),DateTime_ DateTime,Type varchar(10))
    insert into #temp (ID,DateTime_,Type) values ('1001','2020-01-01 08:07:24.000','I')
    ,('1001','2020-01-01 08:15:24.000','I')
    ,('1001','2020-01-01 10:07:24.000','O')
    ,('1001','2020-01-01 11:07:24.000','I')
    ,('1001','2020-01-01 16:07:24.000','O')
    ,('1001','2020-01-01 16:37:24.000','O')
    ,('1002','2020-01-01 08:07:24.000','I')
    ,('1002','2020-01-01 08:17:24.000','I')
    ,('1002','2020-01-01 14:07:24.000','O')
    ,('1002','2020-01-01 15:07:24.000','O')
    ,('1002','2020-01-01 15:10:24.000','I')
    ,('1002','2020-01-01 17:07:24.000','O')
    ,('1002','2020-01-01 19:07:24.000','O');
    
    --Get only First In and First Out Only
    ;with mycte as 
    (select *
    ,
    row_number() Over( partition by ID order by DateTime_)
    -row_number() Over( partition by ID,Type order by DateTime_)
    grp
    from #temp
    )
    ,mycte2 as (
    select * ,
    row_number() Over( partition by ID,Type, grp order by DateTime_) rn
    from mycte
    )
    select ID,DateTime_,Type 
    from mycte2
    where rn=1
    Order by ID,Datetime_
    
    
    --get First in Last Out data only
    ;with mycte as 
    (select *
    ,
    row_number() Over( partition by ID order by DateTime_)
    -row_number() Over( partition by ID,Type order by DateTime_)
    grp
    from #temp
    )
    ,mycte2 as (
    select * ,
    row_number() Over( partition by ID,Type, grp order by DateTime_) rnASC
    ,row_number() Over( partition by ID,Type, grp order by DateTime_ DESC) rnDesc
    from mycte
    )
    select ID,DateTime_,Type 
    from mycte2
    where (Type='I' and rnASC=1)
    or (Type='O' and rnDESC=1)
    Order by ID,Datetime_
    
    
    
    drop table #temp


    Monday, January 20, 2020 3:43 PM
    Moderator