SQL to get First, Last and Largest Transaction based on Transaction Amount and Date

The below query helps an user to get more details of transaction like first, last transaction, largest transaction based on transaction amount, etc.

1. Consider a table dbo.TransactionSummary as shown below:

TransactionID	AccountID	TransDate	TransType	BranchID	TransAmount
1001	11234	2000-01-01	Direct	1	1200
1002	11235	2001-03-02	Direct	1	2000
1003	11234	2001-05-12	Direct	2	1345
1004	11234	2002-05-23	Direct	1	2050
1005	11234	2002-07-23	Indirect	2	1000
1006	11235	1999-09-12	Indirect	1	2000
1007	11235	2010-09-17	Indirect	1	3000

Now, I want to get the First Trans Date, First Trans Amount, Last Trans Date, Last Trans Amount, Largest Trans Date (Date on which largest transaction took place) and Largest Trans Amount for each AccountID grouped by TransType, BranchID.

The below query helps to get those details:

WITH TransData AS (
 SELECT  AccountID
                 , TransType
                 , BranchID
                 , MIN(CONVERT(VARCHAR,TS.[TransDate],120) + '*'+ CONVERT(VARCHAR,TS.[TransAmount]) ) [FirstTransactionData]
                 , MAX(CONVERT(VARCHAR,TS.[TransDate],120) + '*'+ CONVERT(VARCHAR,TS.[TransAmount]) ) [LastTransactionData]
                , MAX(CONVERT(BINARY(10),TS.TransAmount)+ CONVERT(BINARY(10),TS.[TransDate])) [LargestTransactionData]
FROM dbo.TransactionSummary
GROUP BY  AccountID
                      , TransType
                      , BranchID)
 SELECT  AccountID
                  , TransType
                  , BranchID
                  , SUBSTRING(@FirstTransactionData, 1, 10) AS FirstTransDate
                  , SUBSTRING(@FirstTransactionData,CHARINDEX('*',@FirstTransactionData)+1, 20) [FirstTransAmount]
                 , SUBSTRING(@LastTransactionData, 1, 10) AS LastTransDate
                 , SUBSTRING(@LastTransactionData,CHARINDEX('*',@LastTransactionData)+1, 20) [LastTransAmount]
                , CONVERT(DATE,SUBSTRING(@LargestTransactionData,11,10)) [LargestTransDate]
                , CONVERT(MONEY,SUBSTRING(@LargestTransactionData, 1,10)) [LargestTransAmount]
FROM TransData