I have 2 tables containing roll no and policy numbers in a relation like, 1 roll no can have multiple policies and multiple transactions based on a single roll number.
table A
| id | policy | rollno | tax |
|---|---|---|---|
| 1 | P1 | R1 | 60 |
| 2 | P2 | R1 | 30 |
| 3 | P3 | R2 | 120 |
| 4 | P4 | R2 | 35 |
table B
| id | amnt | rollno |
|---|---|---|
| 1 | 30 | R1 |
| 2 | 140 | R1 |
| 3 | 90 | R2 |
| 4 | 65 | R2 |
| 5 | 20 | R2 |
| 6 | 200 | R2 |
| 7 | 130 | R2 |
| 8 | 85 | R2 |
For table B, rollno R2 has 6 transactions. Now the logic will be like, I need a query that will group the transactions based on the nearest tax calculations from the table A with policy number. SO, the result will be like below: For ex: if “amnt” 90 is closer to “tax” 120 and 20 is closer to 35, the results will be grouped accordingly.
table C
| id | amnt | rollno | policy | tax |
|---|---|---|---|---|
| 1 | 30 | R1 | P2 | 30 |
| 2 | 140 | R1 | P1 | 60 |
| 3 | 90 | R2 | P3 | 120 |
| 4 | 65 | R2 | P3 | 120 |
| 5 | 20 | R2 | P4 | 35 |
| 6 | 200 | R2 | P3 | 120 |
| 7 | 130 | R2 | P3 | 120 |
| 8 | 85 | R2 | P3 | 120 |
So far I tried like the below query but it is listing double records when I am trying to query the results. I tried the below query but it is not working. I am not so expert in SQL.
select b.*, a.policy, (select max(a.tax) from tableA a where b.amnt>a.tax) y
from tableB join tableA c on b.rollno=a.rollno
union
select b.*, a.policy, (select min(a.tax) from tableA a where b.amnt<a.tax) y
from tableB join tableA c on b.rollno=a.rollno
8
The closest means the minimum of the absolute value of the difference:
select id, amnt, rollno, policy, tax
from (
select d.*,
min(dist) over(partition by rollno, amnt) as min_dist
from (
select b.id, b.amnt, rollno, a.policy, a.tax,
abs(a.tax - b.amnt) as dist
from table_a a
join table_b b using( rollno )
) d
)
where dist = min_dist
order by id, rollno
;
2