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
ken yupAsked:
Who is Participating?
 
James BilousConnect With a Mentor Software EngineerCommented:
To rephrase:

Given a table of sold items and a table of new orders, you want to find the invoice number for a specific group of sold items?

Unless I'm missing something, it seems like this could be done with a simple join. Let me know if I'm misunderstanding.

SELECT invoiceno FROM incoming_table, sold_item_table 
WHERE incoming_table.prod_no = sold_item_table.prod_no,
incoming_table.batch_no = sold_item_table.batch_no,
incoming_table.vendor = sold_item_table.vendor;

Open in new window

0
 
ken yupAuthor Commented:
the problem has not resolved yet.
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,
0
 
PortletPaulfreelancerCommented:
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.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
PortletPaulConnect With a Mentor freelancerCommented:
This result:
|      INVOICENO |                     IMCOME_DATE |  PROD_NO | BATCH_NO |   VENDOR | AMOUNT | PRICE | TOTAL |
|----------------|---------------------------------|----------|----------|----------|--------|-------|-------|
| dep20141201001 | December, 01 2014 00:00:00+0000 | 12345678 |   abcd12 | abcd Co. |      2 |     1 |     2 |
| dep20141201002 | December, 02 2014 00:00:00+0000 | 12345678 |   abcd12 | abcd Co. |      1 |     1 |     1 |

Open in new window

was produced by this query:
SELECT
      I.*
FROM in_coming I
INNER JOIN sold_item_table S ON I.prod_no = S.prod_no
            AND I.batch_no = S.batch_no
            AND I.vendor = S.vendor
            AND I.imcome_date <= S.sold_date
;

Open in new window

using this data (note data types are derived from the data):
CREATE TABLE sold_item_table
	(`list_no` bigint
     , `sold_date` datetime
     , `prod_no` int
     , `batch_no` varchar(6)
     , `vendor` varchar(8)
     , `amount` int
     , `price` numeric
     , `total` numeric)
;
	
INSERT INTO sold_item_table
	(`list_no`, `sold_date`, `prod_no`, `batch_no`, `vendor`, `amount`, `price`, `total`)
VALUES
	(2015010, '2015-01-02 00:00:00', 12345678, 'abcd12', 'abcd Co.', 2, 2.2, 4.4)
;



CREATE TABLE in_coming
	(`invoiceno` varchar(14)
     , `imcome_date` datetime
     , `prod_no` int
     , `batch_no` varchar(6)
     , `vendor` varchar(8)
     , `amount` int
     , `price` numeric
     , `total` numeric)
;
	
INSERT INTO in_coming
	(`invoiceno`, `imcome_date`, `prod_no`, `batch_no`, `vendor`, `amount`, `price`, `total`)
VALUES
	('dep20141201001', '2014-12-01 00:00:00', 12345678, 'abcd12', 'abcd Co.', 2, 1.1, 2.2),
	('dep20141202002', '2014-12-02 00:00:00', 23456789, 'efgh12', 'efgh Co.', 2, 1.0, 2.0),
	('dep20141201002', '2014-12-02 00:00:00', 12345678, 'abcd12', 'abcd Co.', 1, 1.1, 1.1)
;

Open in new window


see this working at: http://sqlfiddle.com/#!9/1c217/1

NB: Do not understand what the second temp table is or what role it performs
0
 
sarabandeCommented:
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
0
 
Walter RitzelSenior Software EngineerCommented:
Ken,
the best algorithm for that is the SQL query written by James Bilous.
0
 
PortletPaulfreelancerCommented:
@Walter

why "best"?
the comment by James is a simple inner join (but using old style syntax)
0
 
Walter RitzelSenior Software EngineerCommented:
@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.
0
 
PortletPaulfreelancerCommented:
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.
0
 
Walter RitzelSenior Software EngineerCommented:
Yes, I agree that the requirement needs clarification.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.