none
SSRS 2005 - How to select the most recent record??

    Question

  • Hi all

    I have the following table (please bear in mind that dates are in dd/mm/yyyy format):

    AssetID        UserName   LendDate
    1                 AA              01/12/2012
    1                 BB              15/12/2012
    1                 CC              18/01/2013
    2                 DD              01/07/2011
    2                 CC               01/08/2011
    2                 AA               09/09/2011

    What I would like to do is end up with:

    AssetID        UserName    LendDate
    1                 CC              18/01/2013
    2                 AA               09/09/2011

    What I am trying to show is, who is the current user for each asset - this equates to the user for each asset with the most recent date

    Each query I have tried to run returns a record for each user and each asset - I only want to know the user with the most recent record for each asset

    My most recent query:

    SELECT     ASSETLENDING.ASSETID, DIRPARTYTABLE.NAME AS UserName, MAX(ASSETLENDING.LENDINGDATE) AS LendDate
                                FROM          ASSETLENDING INNER JOIN
                                                       EMPLTABLE ON ASSETLENDING.DATAAREAID = EMPLTABLE.DATAAREAID AND ASSETLENDING.EMPLID = EMPLTABLE.EMPLID INNER JOIN
                                                       DIRPARTYTABLE ON EMPLTABLE.DATAAREAID = DIRPARTYTABLE.DATAAREAID AND EMPLTABLE.PARTYID = DIRPARTYTABLE.PARTYID
    GROUP BY ASSETLENDING.ASSETID, DIRPARTYTABLE.NAME
    order by assetlending.assetid

    Can anybody help me out please??
    Cheers
    Naz
    Thursday, September 20, 2012 2:43 PM

Answers

  • Hi, 

    The issue with your sql script is the join to cte_2.assetid and cte_2.dateLent the alias should be cte_1 you are not using an alias for CTE_2.

    • Marked as answer by Naz at ANSA Monday, January 21, 2013 4:21 PM
    Monday, January 21, 2013 10:46 AM

All replies

  • You could include ROW_NUMBER in the select statement, which allows to only select the first/last row.

    • Edited by irusul Thursday, September 20, 2012 2:52 PM
    Thursday, September 20, 2012 2:52 PM
  • Give a try to this.

    create table #temp(AssetID int,UserName varchar(10),LendDate datetime)
    set dateformat dmy;
    insert into #temp values
    (1,                 'AA',              '01/12/2012'),
    (1,                'BB',              '15/12/2012'),
    (1,                 'CC',              '18/01/2013'),
    (2,                 'DD',              '01/07/2011'),
    (2,                 'CC',               '01/08/2011'),
    (2,                 'AA',               '09/09/2011')

    ;with CTE
    As
    (
    Select ROW_NUMBER() over (partition by AssetID order by LendDate desc) as RowID,*
    from #temp
    )
    Select AssetID,UserName,LendDate from CTE
    where
    CTE.RowID=1


    Aftab Ansari

    Thursday, September 20, 2012 2:53 PM
  • Hi this easy to do with a cte:


    with cte as
    (Select 1 as Assetid,'AA' as UserName,'01/12/2012' as LendDate union all 
    select 1,'BB','15/12/2012' union all 
    select 1, 'CC','18/01/2013' union all 
    select 2,'DD','01/07/2011' union all 
    select 2,'CC','01/08/2011' union all 
    select 2,'AA','09/09/2011'), 
    ----get the assetid and lenddate
    cte1 as (Select distinct AssetID, max(Lenddate) as Lenddate from cte group by AssetID)

    select assetid,
    (select username from cte where cte.assetid=cte.assetid and cte.lenddate = cte1.lenddate) as username,
    lenddate
    from cte1 

    Thursday, September 20, 2012 4:20 PM
  • I cant get cte function to work in SSRS, not sure how to combine it with my query above?? Is CTE supported in SSRS?

    Friday, September 21, 2012 7:54 AM
  • Hi Naz,

    Yes CTE work works in SSRS.

    Give a try to below query.

    ;with CTEMaxDate
    As
    (
    Select ROW_NUMBER() over (partition by AssetID order by LendDate desc) as RowID,*
    from ASSETLENDING
    )

    SELECT    
    ASSETLENDING.ASSETID, DIRPARTYTABLE.NAME AS UserName, ASSETLENDING.LENDINGDATE AS LendDate
    FROM
    ASSETLENDING INNER JOIN EMPLTABLE
    ON ASSETLENDING.DATAAREAID = EMPLTABLE.DATAAREAID AND ASSETLENDING.EMPLID = EMPLTABLE.EMPLID
    INNER JOIN DIRPARTYTABLE
    ON EMPLTABLE.DATAAREAID = DIRPARTYTABLE.DATAAREAID AND EMPLTABLE.PARTYID = DIRPARTYTABLE.PARTYID
    Inner Join CTEMaxDate
    ON CTEMaxDate.AssetID=ASSETLENDING.AssetID And CTEMaxDate.UserName=DIRPARTYTABLE.NAME and CTEMaxDate.RowID=1


    Aftab Ansari

    Friday, September 21, 2012 10:38 AM
  • Hi Aftab, I get an error saying the OVER construct is not supported??

    I have tried to implement the advice from Nasa1999, but get an error. The following is my full query at present:

    WITH CTE AS (SELECT DISTINCT ASSETID, MAX(LENDINGDATE) AS DateLent, DATAAREAID
                                   FROM          ASSETLENDING
                                   WHERE      (DATAAREAID IN (@CompanyID))
                                   GROUP BY DATAAREAID, ASSETID)
        SELECT     ASSETTABLE.ASSETID, (SELECT     DIRPARTYTABLE.NAME
                                      FROM          ASSETLENDING AS ASSETLENDING_1 INNER JOIN
                                                             EMPLTABLE ON ASSETLENDING_1.DATAAREAID = EMPLTABLE.DATAAREAID AND ASSETLENDING_1.EMPLID = EMPLTABLE.EMPLID INNER JOIN
                                                             DIRPARTYTABLE ON EMPLTABLE.DATAAREAID = DIRPARTYTABLE.DATAAREAID AND EMPLTABLE.PARTYID = DIRPARTYTABLE.PARTYID
                                      WHERE      (ASSETLENDING_1.ASSETID = CTE_2.assetid) AND (ASSETLENDING_1.LENDINGDATE = cte_2.DateLent) AND 
                                                             (ASSETLENDING_1.DATAAREAID IN (@CompanyID))) AS LastUser, CTE_1.DateLent
         FROM         ASSETTABLE INNER JOIN
                                ASSETBOOK ON ASSETTABLE.ASSETID = ASSETBOOK.ASSETID AND ASSETTABLE.DATAAREAID = ASSETBOOK.DATAAREAID INNER JOIN
                                ASSETMAJORTYPE ON ASSETTABLE.MAJORTYPE = ASSETMAJORTYPE.MAJORTYPE AND 
                                ASSETTABLE.DATAAREAID = ASSETMAJORTYPE.DATAAREAID INNER JOIN
                                CTE AS CTE_1 ON ASSETTABLE.DATAAREAID = CTE_1.DATAAREAID AND ASSETTABLE.ASSETID = CTE_1.ASSETID
         WHERE     (ASSETTABLE.DATAAREAID IN (@CompanyID)) AND (ASSETTABLE.ASSETGROUP = N'MV') AND (ASSETBOOK.STATUS IN (@Status)) AND 
                                (ASSETMAJORTYPE.MAJORTYPE IN (@Type))
         ORDER BY ASSETTABLE.ASSETID

    Each time I run this I get the following error:

    Msg 4104, Level 16, State 1, Line 9
    The multi-part identifier "CTE_2.assetid" could not be bound.
    Msg 4104, Level 16, State 1, Line 9
    The multi-part identifier "cte_2.DateLent" could not be bound.

    Any ideas what I am doing wrong??
    Naz

    Friday, September 21, 2012 11:08 AM
  • The message I get when trying to use the Over statement is:

    The OVER SQL construct or statement is not supported

    Friday, September 21, 2012 11:39 AM
  • Hi Naz,

    i will suggest to post your T-sql problem on  T-Sql Forum for quick response.


    Aftab Ansari

    Friday, September 21, 2012 12:58 PM
  • Hi, 

    The issue with your sql script is the join to cte_2.assetid and cte_2.dateLent the alias should be cte_1 you are not using an alias for CTE_2.

    • Marked as answer by Naz at ANSA Monday, January 21, 2013 4:21 PM
    Monday, January 21, 2013 10:46 AM