is there a better algorithms for this case?

Posted on 2015-02-12
Last Modified: 2015-05-08
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
Question by:ken yup
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

James Bilous earned 250 total points
ID: 40605808
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


Author Comment

by:ken yup
ID: 40606872
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,
LVL 49

Expert Comment

ID: 40607603

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.
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

LVL 49

Assisted Solution

PortletPaul earned 250 total points
ID: 40607747
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:
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`)
	(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`)
	('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:!9/1c217/1

NB: Do not understand what the second temp table is or what role it performs
LVL 34

Expert Comment

ID: 40612139
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.  

LVL 16

Expert Comment

by:Walter Ritzel
ID: 40648077
the best algorithm for that is the SQL query written by James Bilous.
LVL 49

Expert Comment

ID: 40648109

why "best"?
the comment by James is a simple inner join (but using old style syntax)
LVL 16

Expert Comment

by:Walter Ritzel
ID: 40648932
@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.

LVL 49

Expert Comment

ID: 40648978
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.
LVL 16

Expert Comment

by:Walter Ritzel
ID: 40648983
Yes, I agree that the requirement needs clarification.

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (  It addresses one of the most common problems that plague beginning PHP develop…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question