Skip to main content
3 of 6
Updated based on comment
Thomas Kejser
  • 6.2k
  • 2
  • 23
  • 46

This does it in a single table scan:

SELECT Account_ID
  , MAX(DATEDIFF(day, Min_Date, Tran_Date)) AS Days_Passed 
  , MAX(Tran_Amount) AS Max_Amount
FROM (
  SELECT Account_ID
     , Tran_Date
     , Tran_Amount
     , MIN(Tran_Date) OVER ( PARTITION BY Account_ID) AS Min_Date
     , MAX(Tran_Amount) OVER (PARTITION BY Account_ID) AS Max_Amount
  FROM trans
  GROUP BY Account_ID, Tran_Date, Tran_Amount
) I
WHERE I.Tran_Amount = I.Max_Amount 
  OR I.Tran_Date = I.Min_Date
GROUP BY Account_ID
  

This query assumes that only a few rows are found per Account_ID. You can improve the speed of the query (which does a sort) by adding Tran_Amount to the index. While your question does say you cannot edit the schema, at least you can have a discussion with the DBA if you see an improvement on experimental data.

Thomas Kejser
  • 6.2k
  • 2
  • 23
  • 46