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

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 48

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.
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

LVL 48

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 33

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 48

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 48

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot Rows To Columns 10 53
Insert values are dynamic 11 41
Please help with the below query - SQL Server 11 18
MySQL Backup Strategy 14 21
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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