Problem Statement:-

Transatcion_tbl Table has four columns CustID, TranID, TranAmt, and TranDate. User has to display all these fields along with maximum TranAmt for each CustID and ratio of TranAmt and maximum TranAmt for each transaction.

INPUT:


EXPECTED OUTPUT:




Table and Insert Script _____________________________________________ CREATE TABLE [dbo].[Transaction_Tbl]( [CustID] [int] , [TranID] [int] , [TranAmt] [float] , [TranDate] [date] ) INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20001, 10000, CAST('2020-04-25' AS Date)) INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20002, 15000, CAST('2020-04-25' AS Date)) INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20003, 80000, CAST('2020-04-25' AS Date)) INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1001, 20004, 20000, CAST('2020-04-25' AS Date)) INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30001, 7000, CAST('2020-04-25' AS Date)) INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30002, 15000, CAST('2020-04-25' AS Date)) INSERT [dbo].[Transaction_Tbl] ([CustID], [TranID], [TranAmt], [TranDate]) VALUES (1002, 30003, 22000, CAST('2020-04-25' AS Date))

Solution 1:

select CustID, TranID, TranAmt, TranDate, MAX(TranAmt) OVER(PARTITION BY CustID ORDER BY TranAmt DESC) as MaxTranAmt, CAST((TranAmt/MAX(TranAmt) OVER(PARTITION BY CustID ORDER BY TranAmt DESC)) as DECIMAL(10,2)) as Ratio from [dbo].[Transaction_Tbl];

Solution 2:
select A.CustID, A.TranID, A.TranAmt, A.TranDate, B.MaxTranAmt, CAST((A.TranAmt/B.MaxTranAmt) as DECIMAL(10,2)) as Ratio from [dbo].[Transaction_Tbl] A INNER JOIN ( SELECT CustID, MAX(TranAmt) as MaxTranAmt FROM [dbo].[Transaction_Tbl] GROUP BY CustID ) as B ON A.CustID=B.CustID;