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
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
;