none
Remove First OUT value in and Duplicate value RRS feed

  • Question

  • Hi,

    I want remove is first row checked type='O'

    and some rows i want duplicate text


    create table #temp (EmpID varchar(20),CheckedTime datetime,CheckedType varchar(1))
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 08:00:10.000','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 09:00:10.000','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 12:55:00.305','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:40:20.111','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:50:20.111','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:25:15.224','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:58:00.003','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 15:10:30.030','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:40:40.156','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:45:00.601','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:54:00.601','O')

    ;with cte as(
    select *,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime) as rn
    ,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime Desc) as rn2
    from #temp
    )

    select c1.EmpID,c1.CheckedTime,c1.CheckedType,
    case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end as flag
    from cte c1
    left join cte c2 on c1.EmpID=c2.EmpID and c1.rn=c2.rn+1
    left join cte c3 on c1.EmpID=c3.EmpID and c1.rn2=c3.rn2+1
    order by EmpID,CheckedTime
    drop table #temp

    Wednesday, September 27, 2017 6:20 AM

All replies

  • See this query based on your query

    ;with cte as(
    select *,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime) as rn
    ,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime Desc) as rn2
    from #temp
    )
    ,cte2
    as
    (
    select row_number () over (partition by c1.EmpID,c1.CheckedType order by c1.CheckedTime) rn ,c1.EmpID,c1.CheckedTime,c1.CheckedType,
    case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end as flag
    from cte c1
    left join cte c2 on c1.EmpID=c2.EmpID and c1.rn=c2.rn+1
    left join cte c3 on c1.EmpID=c3.EmpID and c1.rn2=c3.rn2+1

    ) select * from cte2 where rn>1  OR  CheckedType='I'
    order by EmpID,CheckedTime
    ---drop table #temp


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, September 27, 2017 6:40 AM
    Answerer
  • Hi Farook,

    What did you mean that you want to duplicate text? What is the text? Please share us the logic and your expected result.

    Then to remove the first out row, please refer to following query. See if it works for you:

    ;with cte as(
    select *,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime) as rn
    ,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime Desc) as rn2
    from #temp
    ),
    cte2 as(
    select c1.EmpID,c1.CheckedTime,c1.CheckedType,
    case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end as flag,
    Case when ROW_NUMBER()Over(Partition By c1.EmpID Order By c1.CheckedTime)=1 and c1.CheckedType='O' then 1 else 0 end firstout
    from cte c1
    left join cte c2 on c1.EmpID=c2.EmpID and c1.rn=c2.rn+1
    left join cte c3 on c1.EmpID=c3.EmpID and c1.rn2=c3.rn2+1
    --order by EmpID,CheckedTime
    )
    
    select *
    from cte2 
    where firstout<>1 

    Thanks,
    Xi Jin.


    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.


    • Edited by Xi Jin Wednesday, September 27, 2017 6:56 AM
    • Proposed as answer by Xi Jin Monday, October 9, 2017 9:37 AM
    Wednesday, September 27, 2017 6:56 AM
  • create table #temp (EmpID varchar(20),CheckedTime datetime,CheckedType varchar(1))
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 08:00:10.000','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 09:00:10.000','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 12:55:00.305','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:40:20.111','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:50:20.111','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:25:15.224','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:58:00.003','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 15:10:30.030','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:40:40.156','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:45:00.601','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:54:00.601','O')
    
    ;with cte as(
    select *,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime) as rn
    ,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime Desc) as rn2
    ,ROW_NUMBER()OVER (PARTITION BY EmpID,CheckedType ORDER BY CheckedTime ) as rn3
    from #temp
    )
    
    select c1.EmpID,c1.CheckedTime,c1.CheckedType,
    case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end as flag 
    from cte c1
    left join cte c2 on c1.EmpID=c2.EmpID and c1.rn=c2.rn+1
    left join cte c3 on c1.EmpID=c3.EmpID and c1.rn2=c3.rn2+1
    WHERE (c1.rn3>1 and c1.CheckedType='O') or c1.CheckedType<>'O'
    order by c1.EmpID,c1.CheckedTime
    drop table #temp
    

    Wednesday, September 27, 2017 1:35 PM
    Moderator
  • create table #temp (EmpID varchar(20),CheckedTime datetime,CheckedType varchar(1))
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 08:00:10.000','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 09:00:10.000','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 12:55:00.305','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:40:20.111','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 13:50:20.111','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:25:15.224','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 14:58:00.003','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 15:10:30.030','I')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:40:40.156','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:45:00.601','O')
    insert into #temp (EmpID,CheckedTime,CheckedType) values ('E1001','2017-09-12 16:54:00.601','O')

    ;with cte as(
    select *,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime) as rn
    ,ROW_NUMBER()OVER (PARTITION BY EmpID ORDER BY CheckedTime Desc) as rn2
    ,ROW_NUMBER()OVER (PARTITION BY EmpID,CheckedType ORDER BY CheckedTime ) as rn3
    from #temp
    )
    ,mycte2 as(
    select c1.EmpID,c1.CheckedTime,c1.CheckedType,
    case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end as flag 
     ,ROW_NUMBER()OVER (PARTITION BY c1.EmpID,
     case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end  ORDER BY c1.CheckedTime DESC) as rn4
    ,SUM(Case when case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end is not null then 1 else 0 end )OVER (PARTITION BY c1.EmpID,
     case when c3.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'FI'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='I' then 'LI'
    when c3.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'FO'
    when c2.CheckedType=c1.CheckedType and c1.CheckedType='O' then 'LO'
    else null
    end ) cnt
    from cte c1
    left join cte c2 on c1.EmpID=c2.EmpID and c1.rn=c2.rn+1
    left join cte c3 on c1.EmpID=c3.EmpID and c1.rn2=c3.rn2+1
    WHERE (c1.rn3>1 and c1.CheckedType='O') or c1.CheckedType<>'O'
    )

    select EmpID,CheckedTime,CheckedType,flag from mycte2
    WHERE Not (rn4=1 and cnt=2)
    order by  EmpID,  CheckedTime
    drop table #temp


    Wednesday, September 27, 2017 1:48 PM
    Moderator
  • Try:

    SELECT
        EmpID,
        CheckedTime,
        CheckedType,
        CASE
        WHEN (
    	CheckedType = LAG(CheckedType) OVER(PARTITION BY EmpID ORDER BY CheckedTime)
    	AND CheckedType = LEAD(CheckedType) OVER(PARTITION BY EmpID ORDER BY CheckedTime)
    	)
    	OR CheckedType = 'O' AND LAG(CheckedType) OVER(PARTITION BY EmpID ORDER BY CheckedTime) IS NULL THEN 1
        ELSE
    	0
        END AS del_flag
    FROM
        #temp
    ORDER BY
        EmpID,
        CheckedTime;
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    Wednesday, September 27, 2017 1:50 PM
    Moderator