CODEWITHSUNDEEP
sqlsql-servermax
Gav_Ed
Gav_Ed

Reputation: 41

selecting max values

I've created the below query to retrieve various Client and Matter information for my law firm. - Essentially we need to see who has worked on which matter(s) for which client(s) in the last three years.

In addition to the Matter Creation Date, we've now been asked to retreive the Transaction Date - TT.TRAN_DATE - (billed hours) which will let us see who has worked on something recently with a much greater degree of accuracy. Problem is that there will be multiple transaction dates for each client/matter/fee earner and we only really need to see the most recent transaction date.

Unfortunately I'm struggling and time is short. Can someone tell me how I can modify the above query so that only the maximum transaction date will be returned for each client / matter / fee earner? The date should also be in the same format as the existing 'Matter Creation Date' field.

Thanks in advance, Gavin

SELECT DISTINCT HC.CLIENT_NAME AS [Client Name],
  HC.CLIENT_CODE AS [Client Code],
  HM.MATTER_CODE AS [Matter Code], 
  HM.CLNT_MATT_CODE AS [Client Matter Code],
  convert(varchar,HM.OPEN_DATE,103) AS [Matter Creation Date],
  HP.EMPLOYEE_CODE AS [Fee Earner Code],
  HP.EMPLOYEE_NAME AS [Fee Earner Name],
  TT.TRAN_DATE AS [Transaction Date]
  FROM        HBM_MATTER HM  
  LEFT OUTER JOIN TAT_TIME TT 
        ON    HM.MATTER_UNO=TT.MATTER_UNO
  LEFT JOIN   HBM_CLIENT HC 
        ON    HM.CLIENT_UNO=HC.CLIENT_UNO 
  LEFT JOIN   HBM_PERSNL HP 
        ON    TT.TK_EMPL_UNO=HP.EMPL_UNO 
  WHERE             HM.INACTIVE='n' 
  AND         HM.STATUS_CODE <> 'CLOSE'
  AND         HP.INACTIVE = 'N'
  AND         TT.TRAN_DATE > dateadd(month,-36,getdate()) 
  ORDER BY    HC.CLIENT_CODE,   HM.MATTER_CODE

Upvotes: 1

Views: 98

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

Assuming SQL Server because you didn't specify, this is my solution. I can't check it but I think it should be OK:

    WITH cte AS (SELECT DISTINCT 
      HC.CLIENT_NAME AS [Client Name]
      ,HC.CLIENT_CODE AS [Client Code]
      ,HM.MATTER_CODE AS [Matter Code]
      ,HM.CLNT_MATT_CODE AS [Client Matter Code]
      ,CONVERT(VARCHAR,HM.OPEN_DATE,103) AS [Matter Creation Date]
      ,HP.EMPLOYEE_CODE AS [Fee Earner Code]
      ,HP.EMPLOYEE_NAME AS [Fee Earner Name]
      ,TT.TRAN_DATE AS [Transaction Date]
      ,ROW_NUMBER() OVER(PARTITION BY HC.CLIENT_CODE, HM.MATTER_CODE, HP.EMPLOYEE_CODE 
                          ORDER BY TT.TRAN_DATE DESC) AS trans_order 
    FROM HBM_MATTER HM  
      LEFT OUTER JOIN TAT_TIME TT ON HM.MATTER_UNO=TT.MATTER_UNO
      LEFT OUTER JOIN HBM_CLIENT HC ON HM.CLIENT_UNO=HC.CLIENT_UNO 
      LEFT OUTER JOIN HBM_PERSNL HP ON TT.TK_EMPL_UNO=HP.EMPL_UNO 
    WHERE HM.INACTIVE = 'n' 
      AND HM.STATUS_CODE <> 'CLOSE'
      AND HP.INACTIVE = 'N'
      AND TT.TRAN_DATE > DATEADD(MONTH, -36, GETDATE()) 
   )
    SELECT * FROM cte 
    WHERE trans_order = 1
     ORDER BY 
      CLIENT_CODE
      ,MATTER_CODE

Upvotes: 2

Related Questions