sqloracle

How to query based on related tables and grouping the records as per the nearest range values?

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
Solution

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