none
How to filter those ids who both have 0 and 1 RRS feed

  • Question

  • Hello everyone, 

    I have student and exam ids and dates. How can I filter only those student who has both Exam IDs, keep only latest 1st and 2nd exams, and 1st latest exam dose not come prior to 2nd exam, it should not be include, consider not having 2nd exam


    • If any of examID is missing(1 or 2) exclude those StudentId 
    • If they have multiple exam id, take most recent exam 
    • ExamID 1 should come before ExamID term 2 for example if a student gave 2nd exam(1/11/2018) prior to latest 1st(1/02/2019) exam, it will consider as not giving latest 2nd exam, and should be excluded.    

    Data look like this:

    StudentID | ExamID | Date

    10000000 |0| 2014-11-30
    10000000 |0| 2017-07-14
    10000000 |0| 2017-08-01
    10000000 |1| 2017-08-17
    10000000 |1| 2017-08-18
    1000000000|0|2016-09-22
    1000000000|0|2016-09-27
    1000000000|1|2016-10-13
    1000000000|1|2016-10-14
    1001000000|0|2016-06-02
    1003000000|0|2016-07-20
    1003000000|0|2016-08-05
    1005000000|0|2016-08-01
    1005000000|0|2016-08-02
    1005000000|1|2016-08-18
    1005000000|1|2016-08-19
    1007000000|0|2016-06-21
    1012000000|0|2016-08-18

    Output should look like this:

    StudentID | ExamID | Date

    10000000|0|2017-08-01
    10000000|1|2017-08-18
    1000000000|0|2016-09-27
    1000000000|1|2016-10-14
    1005000000|0|2016-08-02
    1005000000|1|2016-08-19


    Will appreciate your help. 

    Thanks, 

    Imran







    Friday, July 19, 2019 11:06 PM

Answers

  • Hi Imran.

    declare @Students as table
    (
    	StudentID int
    	, ExamID bit
    	, Date datetime
    )
    insert into @Students (StudentID,ExamID,Date) values
    	(10000000,0,'20141130')
    	, (10000000,0,'20170714')
    	, (10000000,0,'20170801')
    	, (10000000,1,'20170817')
    	, (10000000,1,'20170818')
    	, (1000000000,0,'20160922')
    	, (1000000000,0,'20160927')
    	, (1000000000,1,'20161013')
    	, (1000000000,1,'20161014')
    	, (1001000000,0,'20160602')
    	, (1003000000,0,'20160720')
    	, (1003000000,0,'20160805')
    	, (1005000000,0,'20160801')
    	, (1005000000,0,'20160802')
    	, (1005000000,1,'20160818')
    	, (1005000000,1,'20160819')
    	, (1007000000,0,'20160621')
    	, (1012000000,0,'20160818')
    
    ;with cte as
    (
    	select
    		row_number() over (partition by StudentID, ExamID order by Date desc) as rn
    		, *
    	from
    		@Students
    )
    , cte_2 as
    (
    	select
    		count(rn) over (partition by StudentID) as cnt
    		, *
    	from
    		cte
    	where
    		rn=1
    )
    select * from cte_2 where cnt>1
    

    Saturday, July 20, 2019 8:22 AM

All replies

  • Hi Imran.

    declare @Students as table
    (
    	StudentID int
    	, ExamID bit
    	, Date datetime
    )
    insert into @Students (StudentID,ExamID,Date) values
    	(10000000,0,'20141130')
    	, (10000000,0,'20170714')
    	, (10000000,0,'20170801')
    	, (10000000,1,'20170817')
    	, (10000000,1,'20170818')
    	, (1000000000,0,'20160922')
    	, (1000000000,0,'20160927')
    	, (1000000000,1,'20161013')
    	, (1000000000,1,'20161014')
    	, (1001000000,0,'20160602')
    	, (1003000000,0,'20160720')
    	, (1003000000,0,'20160805')
    	, (1005000000,0,'20160801')
    	, (1005000000,0,'20160802')
    	, (1005000000,1,'20160818')
    	, (1005000000,1,'20160819')
    	, (1007000000,0,'20160621')
    	, (1012000000,0,'20160818')
    
    ;with cte as
    (
    	select
    		row_number() over (partition by StudentID, ExamID order by Date desc) as rn
    		, *
    	from
    		@Students
    )
    , cte_2 as
    (
    	select
    		count(rn) over (partition by StudentID) as cnt
    		, *
    	from
    		cte
    	where
    		rn=1
    )
    select * from cte_2 where cnt>1
    

    Saturday, July 20, 2019 8:22 AM
  • Hi Imran,

    I use 'pivot' firstly to remove rows which do not contain both '1' and '2' , and 'unpivot' to achieve the final goal. 

    Here it is: 

    IF OBJECT_ID('t1') IS NOT NULL drop table t1
    create table t1(
    StudentID  int, ExamID int, Date date) 
    go
    insert into t1 values 
    (10000000,0, '2014-11-30'),
    (10000000,0,'2017-07-14'),
    (10000000,0,'2017-08-01'),
    (10000000,0,'2017-08-01'),
    (10000000,1,'2017-08-17'),
    (10000000,1,'2017-08-18'),
    (1000000000,0,'2016-09-22'),
    (1000000000,0,'2016-09-27'),
    (1000000000,1,'2016-10-13'),
    (1000000000,1,'2016-10-14'),
    (1001000000,0,'2016-06-02'),
    (1003000000,0,'2016-07-20'),
    (1003000000,0,'2016-08-05'),
    (1005000000,0,'2016-08-01'),
    (1005000000,0,'2016-08-02'),
    (1005000000,1,'2016-08-18'),
    (1005000000,1,'2016-08-19'),
    (1007000000,0,'2016-06-21'),
    (1012000000,0,'2016-08-18'),
    (1088000000,1,'2016-08-18')
    
    
    
    ;with cte as (
    select * from (
    select StudentID,ExamID, max(date)as[max]
    from t1
    group by StudentID,ExamID)a
    pivot (max([max])for ExamID in ([0],[1])) as pvt 
    where [1] is not null and [0] is not null) 
    
    select a.StudentID,a.ExamID,a.date
    from cte c
    unpivot
    (date for ExamID in ([0],[1]))a
    
    /*
    StudentID   ExamID   date
    ----------- -------------------
    10000000    0        2017-08-01
    10000000    1        2017-08-18
    1000000000  0        2016-09-27
    1000000000  1        2016-10-14
    1005000000  0        2016-08-02
    1005000000  1        2016-08-19
    */

    Sabrina


    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, July 22, 2019 6:24 AM