For each account, I need the first transaction date, the largest transaction amount, and the time taken to get from first to largest. Data is truncated and reloaded frequently, and I have read-only access (no schema or index changes). The table has well over a million rows, so creating and updating a temp table is way too slow; cross apply seems inefficient as well. With a CTE and window function I only have to hit the table twice. But is there a better approach? Simplified example of the table and my query follow:
CREATE TABLE [dbo].[trans]( [Tran_ID] [int] IDENTITY(1,1) NOT NULL, [Account_ID] [varchar](50) NULL, [Tran_Date] [datetime] NOT NULL, [Tran_Amount] [money] NOT NULL ) ON [PRIMARY];
EDIT: @Peter the table has the below clustered index. This is the only index, there are no primary or foreign keys or other constraints.
CREATE CLUSTERED INDEX [idx_trans_ID_DATE] ON [dbo].[trans] ( [Account_ID] ASC, [Tran_Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO WITH HighestTran AS ( SELECT Account_ID, Tran_Date, Tran_Amount, ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Date ASC) AS FirstTranRow, ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Amount DESC) AS MaxTranRow FROM dbo.trans WHERE Tran_Amount > 0 ) SELECT t1.Account_ID, t1.Tran_Date, t2.Tran_Date, t1.Tran_Amount, t2.Tran_Amount FROM HighestTran t1 INNER JOIN HighestTran t2 ON t1.Account_ID = t2.Account_ID AND t1.FirstTranRow = 1 and t2.MaxTranRow = 1 ORDER BY t1.Account_ID;