Thursday, November 13, 2008

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.

 

1 comments:

adnan August 3, 2009 at 4:08 PM  

Thx a lot man, this should help me in my project, thx a lot again