hi, Sql Server - rextester

hi

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'

Create Table Customer (Id int PRIMARY KEY Not Null, Name varchar(10));

insert into Customer Values(001,'Pooja')
insert into Customer Values(002,'Priya')
insert into Customer Values(003,'Seema')
insert into Customer Values(004,'John')
insert into Customer values(005,'Federick')

select * from Customer;

Create Table Transaction_table (Trans_id int Not Null,
                          Tran_Amt int Not Null,
                          Trans_Type varchar(10),
                          Id int FOREIGN KEY REFERENCES Customer(Id)
                          )
                          
insert into Transaction_table Values(00010,10000,'CR',001)
insert into Transaction_table Values(00011,20000,'CR',003)
insert into Transaction_table Values(00012,30000,'CR',002)
insert into Transaction_table Values(00013,40000,'DB',004)
insert into Transaction_table Values(00014,50000,'CR',005)

select * from Transaction_table;

SELECT C.ID,C.NAME,TRANS_ID,TRAN_AMT,TRANS_TYPE ,
  CASE WHEN Trans_Type='CR' THEN T.Tran_Amt END as CREDIT,
  CASE WHEN Trans_Type='DB' THEN T.Tran_Amt END as DEBIT	 
  FROM CUSTOMER C
  INNER JOIN TRANSACTION_TABLE T ON C.ID=T.ID

Select Max(Tran_Amt) from TRANSACTION_TABLE where 
Tran_Amt< (select Max(Tran_Amt) from TRANSACTION_TABLE);

Select TRAN_AMT,ROW NUMBER from (Select TRANS_ID,TRAN_AMT,ROW_NUMBER() OVER ( order by TRAN_AMT desc)from TRANSACTION_TABLE)tran where ROW NUMBER =4;


run \| edit \| history \| help	0