ken yup
asked on
is there a better algorithms for this case?
hello sir
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
.......
#2 sold_item_table
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
best regards
ken
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
.......
#2 sold_item_table
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
best regards
ken
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ken,
you could spend a great deal more time explaining your needs with words, or you could provide:
1. "sample data", and
2. "expected result" (from the sample)
The sample you provide in the question may be sufficient for 1. (maybe more data is needed? I don't know)
but we also need the "expected result" in a similar format to that sample.
you could spend a great deal more time explaining your needs with words, or you could provide:
1. "sample data", and
2. "expected result" (from the sample)
The sample you provide in the question may be sufficient for 1. (maybe more data is needed? I don't know)
but we also need the "expected result" in a similar format to that sample.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if we assume that the first table are purchases and the second table are sales, you could try to find out a few things by comparing entries for the same product/batch/vendor and a given time period. for example you could check which sales have been sold profitably and which not. if using the average purchase price given by the matching purchases you can calculate the profit for each sale. or, if there are many purchases compared with many sales, you could try to find out which purchases were used for a given sale. however such a question probably is difficult to decide as you would need to have additional stock information which can only imperfectly retrieved from your tables. to help out from this issue you may apply LIFO principle (last-in, first-out) such that your sales would sell always the last bought goods though this principle probably is not very realistic since a good merchant would try to sell the oldest goods first. for your purposes it may be sufficient nevertheless. if that is your goal you may sort both tables by product-batch and descending date. then you can sequentially read from both result sets and prorate purchases to sales as long as product-batch doesn't change.
if the analysis is not only an exercise but real data, I would recommend to adding stock data to the data base. at the stock level you could find out the value of the current stock for each item from purchases and can compare it with the proceeds you got from sales. it also allows to find out when you have to invoke new orders and which price you need to still make profit.
Sara
if the analysis is not only an exercise but real data, I would recommend to adding stock data to the data base. at the stock level you could find out the value of the current stock for each item from purchases and can compare it with the proceeds you got from sales. it also allows to find out when you have to invoke new orders and which price you need to still make profit.
Sara
Ken,
the best algorithm for that is the SQL query written by James Bilous.
the best algorithm for that is the SQL query written by James Bilous.
@Walter
why "best"?
the comment by James is a simple inner join (but using old style syntax)
why "best"?
the comment by James is a simple inner join (but using old style syntax)
@Portlet Paul
It is the best because is the simplest solution for the problem he have presented us.
The syntax used in this case is irrelevant.
Clearly, the author of this question does not have much experience with SQL.
Att,
Walter.
It is the best because is the simplest solution for the problem he have presented us.
The syntax used in this case is irrelevant.
Clearly, the author of this question does not have much experience with SQL.
Att,
Walter.
Thanks Walter,
is the requirement is satisfied by an inner join?
I remain uncertain about the requirement until it is expressed as an expected result.
is the requirement is satisfied by an inner join?
I remain uncertain about the requirement until it is expressed as an expected result.
Yes, I agree that the requirement needs clarification.
ASKER
firstly i just use first in first out prinspal to handle this situation,i give the sold tables order by sold date,prod no,batch no,vendor,then give the order to the in coming table by income date,prod no,batch no,vendor as well,it's simple to find result.
secondly however,i dont think it's be able to find the result with simply join when the siuation is the relation of sold_item table is many to many with the incoming table, for example ,when the items is good sold,it has sold out in a day,then in came the same prod no,same batch no,same vendor in a same day,however,a new invoice no added,join is not work for such situation.
the only problem is when the case is more situation is many to many,it will use a lot of resource to calculate the invoice no or it takes a lot of time to calculate which invoice no belong to this sold items,so i wonder if have a better algorithms,