Solved

# is there a better algorithms for this case?

Posted on 2015-02-12
144 Views
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
0
Question by:ken yup

LVL 7

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;
``````
0

Author Comment

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,
0

LVL 48

Expert Comment

ID: 40607603
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

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 |
``````
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
;
``````
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)
;
``````

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

LVL 32

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.

Sara
0

LVL 15

Expert Comment

ID: 40648077
Ken,
the best algorithm for that is the SQL query written by James Bilous.
0

LVL 48

Expert Comment

ID: 40648109
@Walter

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

LVL 15

Expert Comment

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.

Att,
Walter.
0

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.
0

LVL 15

Expert Comment

ID: 40648983
Yes, I agree that the requirement needs clarification.
0

## Featured Post

Iteration: Iteration is repetition of a process. A student who goes to school repeats the process of going to school everyday until graduation. We go to grocery store at least once or twice a month to buy products. We repeat this process every mont…
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.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…