By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
| version() |
|---|
| 8.0.25 |
CREATE TABLE Billing (
Bill_Id varchar(10),
SerialNo varchar(10),
BillAmt integer,
Code varchar(5),
DispenseDt DATE
);
INSERT INTO Billing (Bill_ID, SerialNo, BillAmt, Code, DispenseDt)
VALUES ('BL_001','aaa-111',250,'AAP','20200503')
,('BL_002','aab-112',250,'ADD','20200309')
,('BL_003','aab-1x12',-250,'ADD','20200309')
,('BL_004','aba-120',700,'YED','20200503')
,('BL_005','aba-120',370,'TPP','20200822')
,('BL_006','aba-120',370,'TPP','20201003')
,('BL_007','aba-120',400,'TPP','20200822')
,('BL_008','aba-120',-370,'TPP','20200822')
,('BL_009','aba-120',-700,'YED','20200503')
,('BL_010','baa-201',1000,'TOK','20200927')
,('BL_011','baa-201',-1000,'TOK','20200927')
,('BL_012','bab-210',1000,'TOK','20200927');
select * from Billing
| Bill_Id | SerialNo | BillAmt | Code | DispenseDt |
|---|---|---|---|---|
| BL_001 | aaa-111 | 250 | AAP | 2020-05-03 |
| BL_002 | aab-112 | 250 | ADD | 2020-03-09 |
| BL_003 | aab-1x12 | -250 | ADD | 2020-03-09 |
| BL_004 | aba-120 | 700 | YED | 2020-05-03 |
| BL_005 | aba-120 | 370 | TPP | 2020-08-22 |
| BL_006 | aba-120 | 370 | TPP | 2020-10-03 |
| BL_007 | aba-120 | 400 | TPP | 2020-08-22 |
| BL_008 | aba-120 | -370 | TPP | 2020-08-22 |
| BL_009 | aba-120 | -700 | YED | 2020-05-03 |
| BL_010 | baa-201 | 1000 | TOK | 2020-09-27 |
| BL_011 | baa-201 | -1000 | TOK | 2020-09-27 |
| BL_012 | bab-210 | 1000 | TOK | 2020-09-27 |
select
Bill_ID,
Code,
DispenseDt,
new_bill_amt,
rank()
over(partition by new_bill_amt,DispenseDt, code) as rank_,
row_number()
over(partition by new_bill_amt,DispenseDt, code) as rank_2
from (
select
*,
replace(billamt,'-','') as new_bill_amt
from Billing
) as f
| Bill_Id | Code | DispenseDt | new_bill_amt | rank_ | rank_2 |
|---|---|---|---|---|---|
| BL_010 | TOK | 2020-09-27 | 1000 | 1 | 1 |
| BL_011 | TOK | 2020-09-27 | 1000 | 1 | 2 |
| BL_012 | TOK | 2020-09-27 | 1000 | 1 | 3 |
| BL_002 | ADD | 2020-03-09 | 250 | 1 | 1 |
| BL_003 | ADD | 2020-03-09 | 250 | 1 | 2 |
| BL_001 | AAP | 2020-05-03 | 250 | 1 | 1 |
| BL_005 | TPP | 2020-08-22 | 370 | 1 | 1 |
| BL_008 | TPP | 2020-08-22 | 370 | 1 | 2 |
| BL_006 | TPP | 2020-10-03 | 370 | 1 | 1 |
| BL_007 | TPP | 2020-08-22 | 400 | 1 | 1 |
| BL_004 | YED | 2020-05-03 | 700 | 1 | 1 |
| BL_009 | YED | 2020-05-03 | 700 | 1 | 2 |