i have two tables in a database
in my case is
#1 in coming table
invoiceno(pk) imcome_date prod_no(pk) batch_no(pk) vendor(pk) amount price total
dep20141201001 2014-12-01 12345678 abcd12 abcd Co. 2 1.1 2.2
dep20141202002 2014-12-02 23456789 efgh12 efgh Co. 2 1.0 2.0
dep20141201002 2014-12-02 12345678 abcd12 abcd Co. 1 1.1 1.1
list_no (pk) sold_date prod_no(pk) batch_no(pk) vendor(pk) amount price total
20150102001 2015-01-02 12345678 abcd12 abcd Co. 2 2.2 4.4
my question is how the item have been sold in the sold_item table to find the invoice no in incoming table,is it possible there is a better algorithms?
my algorithms is very simple:
firstly in the sold_item table use the cursor select a record ,then use this sold_Date as a deathline to select all the records which are prod_no ,batch_no,vendor equals prod_no,batch_no,vendor in this record and the sell_date equals or smaller than the sold_date into a temporary table.
secondly as well in the incoming table select the all the records which are prod_no ,batch_no,vendor equals prod_no,batch_no,vendor in this record and the imcome_date equals or smaller than the sold_date
into another temporary table
then compare two temporary table records,then i will get the result.
though this solution will get the result,however it's use a lot of resources ,when there are many records,it takes a lot of times to complish,is there a better algorithms
any help would be appeciated!i could search the google or another info with your oppnion