Compare records of Same SQL Table

If you have to compare a record of SQL table to another record (which could be any), you would probably think of a nested while loop. Nested while loop is ok, but it may costlier. Here, I am discussing a trick to avoid loop to compare results.

 

Consider a case of Bank Charge. It is an amount charged by bank for transferring amt to another bank. Here, let's say a manufacturing company has many customers. The customers pay some bank charge while transferring amt from his bank to company's bank. The company pays back some amount to customers against the bank charges. It has own standards to calculate the bank charges.

 

If you are not up to previous example, leave it. Just assume a table with following data.

-- Table Structure

create table tbl_bank_charge

(

            tid                    int identity(1,1),

            customer          varchar(3),

            bank                 varchar(20),

            from_amt          numeric(18,3),

            to_amt              numeric(18,3),

            bc_amt             numeric(18,3)

)

 

-- Table Data

Tid	Customer	Bank	From_Amt	To_Amt	BC_Amt
8	ABC	Nabil	0	50,000	100
9	ABC	Nabil	50,001	100,000	200
10	ABC	RBB	50,000	100,000	333
11	ABC	RBB	200,000	300,000	600
12	SBO	RBB	0	10,000	50
13	ABC	Nabil	1,000	50,000	120
14	SBO	RBB	10,001	90,000	500

 

Check out the data in rows with tid 8 and 13 carefully. Customer ABC is given Rs. 100 for deposit amt of 0 to 50,000 in row with tid 8, while in row with tid 13 it is given Rs 120 for deposit amt of 10,000 to 50,000. This is not correct. How will you find out that? Using nested loop, you will have to compare each row with every another row in table. Now, use the query below to find out overlapping buckets.

 

 

if exists (           select    *

                        from     tbl_bank_charge a

                        inner join tbl_bank_charge b

                                    on        a.customer        = b.customer

                                    and       a.bank              = b.bank

                        where   a.tid      <> b.tid

                        and       (           a.from_amt between b.from_amt and b.to_amt

                                    or         a.to_amt between b.from_amt and b.to_amt)

            )

            begin

                        print 'Error: Buckets Overlap'

                        return

            end

 

It simply joins the same table 2 times and compares each row with another row having same customer and bank. If it finds out any overlapping margins then prints error and returns. Thus, it is a simple tweak to remove loop and optimize query processing.