Return to Answer

This does it in a single table scan:

SELECT Account_ID
  , Min_Date
  , MIN(DATEDIFF(day, Min_Date, NULLIF(Tran_Date, Min_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, Min_Date
  

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.

This does it in a single table scan:

SELECT Account_ID
  , MIN(Min_Date) AS DateFirst
  , MIN(DATEDIFF(day, Min_Date, NULLIF(Tran_Date, Min_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.

This does it in a single table scan:

SELECT Account_ID
  , Min_Date
  , 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, Min_Date
  

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.

This does it in a single table scan:

SELECT Account_ID
  , Min_Date
  , MIN(DATEDIFF(day, Min_Date, NULLIF(Tran_Date, Min_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, Min_Date
  

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.

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.

This does it in a single table scan:

SELECT Account_ID
  , Min_Date
  , 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, Min_Date
  

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.