none
Displaying the most recent row of data

    Question

  • I have query for a report where I am trying to produce a list of all employees and their current mode of travel. The problem I have is for each employee I could have multiple rows for mode of travel. What I want to do is display only the most recent row. The table has field for MOT_date _entered and Mode_of_travel_type. Any one have any suggestions on how I can filter the query so it only display's the most recent row based on MOT_date.

    Table names : Employee, Mode_of_Travel

    Example 

    Employee name      MOT_Date         Mode of travel

    Jon Smith 01/03/2010 Bus

    Jon Smith 24/07/2012 Car

    In the above example I only want the report to display the row date of 24/07/2012  as that is the most recent record.

    Saturday, March 15, 2014 11:41 PM

Answers

  • Its very easy, just a simple query, remplace the field name if necessary :

    Select TOP 1 Employee.employee, MOT_Date, ModeOfTraval From Employee, Mode_of_Travel

    Where Employee.employee = Mode_of_Travel.employee 

    Order by MOT_Date Desc

    :-)

    Sunday, March 16, 2014 4:20 AM
  • I agree to Sathya using filter option in SSRS however it is limited to fixed number of rows. If we can use another dataset containing the max MOT_DATE then we can use lookup/lookup set function in SSRS against the master dataset to display only those rows that has maximum date. Refer

    http://technet.microsoft.com/en-us/library/ee210531.aspx

    http://technet.microsoft.com/en-us/library/ee240819.aspx

    I think using SQL is the best option here for the cases where we have multiple rows for latest date. Below is an alternate option of using TSQL in addition to Sathya,

    DECLARE @Employee_Mode_of_Travel TABLE
    (Employeename VARCHAR(15),MOT_Date VARCHAR(15),Modeoftravel VARCHAR(15))
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','24/07/2012','Car'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','24/07/2012','Car'
    SELECT * FROM (
    	SELECT *,row_number() over (partition by MOT_Date order by MOT_Date desc) rn 
    	FROM @Employee_Mode_of_Travel
    ) X where rn=1


    Regards, RSingh

    Sunday, March 16, 2014 5:31 AM
  • You can write a logic as below in query behind

    SELECT Columns...
    FROM Employee e
    INNER JOIN Mode_Of_Travel t
    ON t.Mode_Of_Travel_Type = e.Mode_Of_Travel_Type
    WHERE NOT EXISTS (SELECT 1
    FROM EMployee
    WHERE Employee_ID = e.EMployee_ID
    AND MOT_Date > e.MOT_Date
    )


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 16, 2014 7:09 AM

All replies

  • Hi,

    DECLARE @Employee_Mode_of_Travel TABLE
    (Employeename VARCHAR(15),MOT_Date VARCHAR(15),Modeoftravel VARCHAR(15))
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','24/07/2012','Car'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','24/07/2012','Car'
    --SELECT * FROM @Employee_Mode_of_Travel
    
    SELECT A.* FROM @Employee_Mode_of_Travel A
    JOIN (
    SELECT Employeename,MAX(MOT_Date) MOT_Date
    FROM @Employee_Mode_of_Travel
    GROUP BY Employeename ) B 
    ON A.Employeename = B.Employeename
    AND A.MOT_Date = B.MOT_Date
                   
    
     
    
    
     
    
    

    or

    To handle in SSRS, goto Tablix Properties... -> Filters -> try like shown below:

     


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Sunday, March 16, 2014 4:07 AM
    Moderator
  • Its very easy, just a simple query, remplace the field name if necessary :

    Select TOP 1 Employee.employee, MOT_Date, ModeOfTraval From Employee, Mode_of_Travel

    Where Employee.employee = Mode_of_Travel.employee 

    Order by MOT_Date Desc

    :-)

    Sunday, March 16, 2014 4:20 AM
  • I agree to Sathya using filter option in SSRS however it is limited to fixed number of rows. If we can use another dataset containing the max MOT_DATE then we can use lookup/lookup set function in SSRS against the master dataset to display only those rows that has maximum date. Refer

    http://technet.microsoft.com/en-us/library/ee210531.aspx

    http://technet.microsoft.com/en-us/library/ee240819.aspx

    I think using SQL is the best option here for the cases where we have multiple rows for latest date. Below is an alternate option of using TSQL in addition to Sathya,

    DECLARE @Employee_Mode_of_Travel TABLE
    (Employeename VARCHAR(15),MOT_Date VARCHAR(15),Modeoftravel VARCHAR(15))
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','24/07/2012','Car'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','24/07/2012','Car'
    SELECT * FROM (
    	SELECT *,row_number() over (partition by MOT_Date order by MOT_Date desc) rn 
    	FROM @Employee_Mode_of_Travel
    ) X where rn=1


    Regards, RSingh

    Sunday, March 16, 2014 5:31 AM
  • You can write a logic as below in query behind

    SELECT Columns...
    FROM Employee e
    INNER JOIN Mode_Of_Travel t
    ON t.Mode_Of_Travel_Type = e.Mode_Of_Travel_Type
    WHERE NOT EXISTS (SELECT 1
    FROM EMployee
    WHERE Employee_ID = e.EMployee_ID
    AND MOT_Date > e.MOT_Date
    )


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, March 16, 2014 7:09 AM
  • Thank you all I will give it a go. Although I have to be honest I am still learning SQL. -:)
    Tuesday, March 18, 2014 10:17 PM
  • Hi,

    DECLARE @Employee_Mode_of_Travel TABLE
    (Employeename VARCHAR(15),MOT_Date VARCHAR(15),Modeoftravel VARCHAR(15))
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT 'Jon Smith','24/07/2012','Car'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','01/03/2010','Bus'
    INSERT @Employee_Mode_of_Travel SELECT ' Smith','24/07/2012','Car'
    --SELECT * FROM @Employee_Mode_of_Travel
    
    SELECT A.* FROM @Employee_Mode_of_Travel A
    JOIN (
    SELECT Employeename,MAX(MOT_Date) MOT_Date
    FROM @Employee_Mode_of_Travel
    GROUP BY Employeename ) B 
    ON A.Employeename = B.Employeename
    AND A.MOT_Date = B.MOT_Date
                   
    
     
    
    
     
    

    or

    To handle in SSRS, goto Tablix Properties... -> Filters -> try like shown below:

     


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Your second option looks to be the easy solution although MOT_date is date field not text, I don't think it will work.
    Tuesday, March 18, 2014 10:20 PM