none
How to get a value's records if its more than two inputs

Answers

  • I am sure what exactly your requirement is. Are you looking for such queries.

    declare @temp table(userid int,checktime datetime,inn char(1),outt char(1))
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    -------------------------Query 1
    select * from (
    select *,row_number() over (partition by userid,inout order by userid) as rowid from (
    select userid,checktime,inn as [IN],outt as [OUT],isnull(inn,'') + isnull(outt,'') as INOUT 
    from @temp
    ) x
    ) y
    where rowid > 2
    -------------------------Query 2
    select userid,max(checktime) as MaxTime,count(inn) as [IN],count(outt) as [OUT] from @temp
    group by userid
    having count(inn)> 2 or count(outt)> 2


    Regards, RSingh

    Tuesday, July 09, 2013 9:10 AM
  • Try this,

    declare @temp table(userid int,checktime datetime,inn char(1),outt char(1))
    insert into  @temp values(144,getdate()-1,'I',null)
    insert into  @temp values(144,getdate()-1,'I',null)
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(143,getdate(),null,'O')
    insert into  @temp values(143,getdate(),null,'O')
    --------------------------------------------
    ;with cte as (
    	select userid,checktime,case 
    	when datediff(d,checktime,getdate()) = 1 then 'Yesterday'
    	when datediff(d,checktime,getdate()) = 0 then 'Today'
    	else 'DayBeforeYesterday'
    	end as checkwhen,inn as [IN],outt as [OUT],isnull(inn,'') + isnull(outt,'') as INOUT
    	from @temp
    ), cte1 as (
    	select *,row_number() over (partition by userid,checkwhen,inout order by userid,checktime,inout) as ranks from cte
    )
    select * from cte1 a where
    a.userid in (select top 1 b.userid from cte1 b where b.userid=a.userid and b.checkwhen=a.checkwhen and b.inout=a.inout
    and b.ranks > 2)


    Regards, RSingh

    Tuesday, July 09, 2013 12:04 PM

All replies

  • can you please explain what output you want by example
    Tuesday, July 09, 2013 8:49 AM
  • can you please explain what output you want by example
    I thought looks like below. Btw, I forgot to mention that two record and more in a row of in or out. That is an attendance data.
    Tuesday, July 09, 2013 8:56 AM
  • I see only tow rows for userid 144

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 09, 2013 9:02 AM
  • I am sure what exactly your requirement is. Are you looking for such queries.

    declare @temp table(userid int,checktime datetime,inn char(1),outt char(1))
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    -------------------------Query 1
    select * from (
    select *,row_number() over (partition by userid,inout order by userid) as rowid from (
    select userid,checktime,inn as [IN],outt as [OUT],isnull(inn,'') + isnull(outt,'') as INOUT 
    from @temp
    ) x
    ) y
    where rowid > 2
    -------------------------Query 2
    select userid,max(checktime) as MaxTime,count(inn) as [IN],count(outt) as [OUT] from @temp
    group by userid
    having count(inn)> 2 or count(outt)> 2


    Regards, RSingh

    Tuesday, July 09, 2013 9:10 AM
  • I see only tow rows for userid 144
    That main screen shot that is not sized with all appears rows. there are 93000 records in that column.
    Tuesday, July 09, 2013 9:10 AM
  • @R.Singh, query 2 is  correct except one condition.. That query retrieves any datetime as counted actually I want to see an exact date in the result. Lets say there are 5 records three of them related yesterday and two of them related today.. then the report must retrieves 3 records from yesterday as IN or Out and two records from today IN or out.
    Tuesday, July 09, 2013 9:30 AM
  • Try this,

    declare @temp table(userid int,checktime datetime,inn char(1),outt char(1))
    insert into  @temp values(144,getdate()-1,'I',null)
    insert into  @temp values(144,getdate()-1,'I',null)
    insert into  @temp values(144,getdate(),'I',null)
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(144,getdate(),null,'O')
    insert into  @temp values(143,getdate(),null,'O')
    insert into  @temp values(143,getdate(),null,'O')
    --------------------------------------------
    ;with cte as (
    	select userid,checktime,case 
    	when datediff(d,checktime,getdate()) = 1 then 'Yesterday'
    	when datediff(d,checktime,getdate()) = 0 then 'Today'
    	else 'DayBeforeYesterday'
    	end as checkwhen,inn as [IN],outt as [OUT],isnull(inn,'') + isnull(outt,'') as INOUT
    	from @temp
    ), cte1 as (
    	select *,row_number() over (partition by userid,checkwhen,inout order by userid,checktime,inout) as ranks from cte
    )
    select * from cte1 a where
    a.userid in (select top 1 b.userid from cte1 b where b.userid=a.userid and b.checkwhen=a.checkwhen and b.inout=a.inout
    and b.ranks > 2)


    Regards, RSingh

    Tuesday, July 09, 2013 12:04 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    It would also help if you knew that rows are not records and could write a clear specifications. We also have a DATE data type, so there is no need to use the 1970's Sybase.UNIX DATETIME today. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 12:58 PM
  • This design flaw is called attribute splitting. Your timestamps get there meaning from another columns; the timestamp is a value of an event. 

    The correct idiom is:

    CREATE TABLE Timesheet
    (user_id INTEGER NOT NULL
      REFERENCES Users(user_id),
     checkin_timestamp DATETIME2(0) NOT NULL,
     PRIMARY KEY (user_id, checkin_timestamp),
     checkout_timestamp DATETIME2(0),
     CHECK (checkin_timestamp < checkout_timestamp),
     ..);



     


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, July 09, 2013 1:10 PM