Copy Code code as follows:
---a problem that is often encountered in warehouse management
First, on the LIFO and FIFO simple explanation
---fifo:first in, first out.
---lifo:last in, first out, LIFO.
-such as goods A: 1st this month to buy 10 pieces, Unit price 10 yuan/piece, 3rd to buy 20, Unit price 15 yuan/piece; 10th to buy 10, Unit price 8 yuan/piece.
--35 shipments shipped this month 15th.
--FIFO advanced first, is the first purchase of inventory issued first, so, the first issue of the 1st purchase of 10, and then issued 3rd purchase of 20 pieces, the last issued on the 10th purchase of 5, issued the cost of a total of: 10*10+20*15+5*8=440 Yuan.
--According to the LIFO first out, that is, after the purchase of inventory first issued, so, the first issue of the 10th purchase of 10, and then issued 3rd 20 of the purchase, the last issue of 5 of the purchase of 1st, issued a total of the cost of: 10*8+20*15+5*10=430 yuan
Second, the example
Copy Code code as follows:
--------
Create Table Stock
(Id int NOT null primary key,
Articleno varchar is not NULL,
Rcvdate datetime NOT NULL,
Qty int NOT NULL,
UnitPrice NOT NULL
)
Go
----
Insert Stock
Select 1, ' 10561122 ', ' 2011-1-1 ', 15,10 Union
Select 2, ' 10561122 ', ' 2011-2-2 ', 25,12 Union
Select 3, ' 10561122 ', ' 2011-3-3 ', 35,15 Union
Select 4, ' 10561122 ', ' 2011-4-4 ', 45,20 Union
Select 5, ' 10561122 ', ' 2011-5-5 ', 55,10 Union
Select 6, ' 10561122 ', ' 2011-6-6 ', 65,30 Union
Select 7, ' 10561122 ', ' 2011-7-7 ', 75,17 Union
Select 8, ' 10561122 ', ' 2011-8-8 ', 110,8
Go
----If you sell 300 products in 2011-8-8, how do you calculate the value of inventory sales?
----1 uses the current replacement cost, the cost of each product in 2011-8-8 is 8, which means that you have 300 products with a cost value of 2400
----2 uses the current average cost unit price, a total of 420, the total cost is 6530, the average cost per item is 15.55
----1.LIFO (backward first out)
----2011-8-8 110 *8
----2011-7-7 75*17
----2011-6-6 65*30
----2011-5-5 50*10
-----Total Cost is 4605
-----2.FIFO (Advanced first Out)
----' 2011-1-1 ', 15*10
---' 2011-2-2 ', 25*12
-----' 2011-3-3 ', 35*15
-----' 2011-4-4 ', 45*20
-----' 2011-5-5 ', 55*10
-----' 2011-6-6 ', 65*30
-----' 2011-7-7 ', 65*17
----Total Cost is 5480
---cost view
CREATE VIEW Costlifo
As
Select UnitPrice from the stock
where rcvdate= (select MAX (rcvdate) from the stock)
Go
CREATE VIEW Costfifo
As
Select SUM (unitprice*qty)/sum (qty) as UnitPrice from the stock
Go
-----Find the most recent date that meets the order's sufficient inventory. If you're lucky, the inventory for one day is exactly the same as the number of orders required.
-----can return the total cost as an answer. If the order stops more than the inventory, nothing returns. If there is more inventory in one day than the order quantity
---then take a look at the current unit price, multiply it by the number, and subtract it.
---The following queries and views simply tell us the inventory value of the inventory, and note that these queries and views are not actually shipped out of inventory.
CREATE VIEW LIFO
As
Select S1.rcvdate,s1.unitprice,sum (S2.qty) as Qty,sum (S2.qty*s2.unitprice) as TotalCost
From the stock S1, the stock s2
where s2.rcvdate>=s1.rcvdate
GROUP BY S1.rcvdate,s1.unitprice
Go
Select (totalcost-(qty-300) *unitprice) as Cost
From LIFO as L
Where rcvdate= (select Max (rcvdate) from LIFO as L2 where qty>=300)
Go
CREATE VIEW FIFO
As
Select S1.rcvdate,s1.unitprice,sum (S2.qty) as Qty,sum (S2.qty*s2.unitprice) as TotalCost
From the stock S1, the stock s2
where s2.rcvdate<=s1.rcvdate
GROUP BY S1.rcvdate,s1.unitprice
Go
Select (totalcost-(qty-300) *unitprice) as Cost
From FIFO as L
Where rcvdate= (select min (rcvdate) from LIFO as L2 where qty>=300)
--------
Go
-----
-----To update inventory table in real time after shipment
CREATE VIEW Currstock
As
Select S1.rcvdate,sum (case when S2.rcvdate>s1.rcvdate then S2.qty else 0-end) as Prvqty
, SUM (case when S2.rcvdate<=s1.rcvdate then S2.qty else 0) as Currqty
From the stock S1, the stock s2
where s2.rcvdate<=s1.rcvdate
GROUP BY S1.rcvdate,s1.unitprice
Go
Create proc Removeqty
@orderqty int
As
if (@orderqty >0)
Begin
Update stock Set Qty =case when @orderqty >= (select Currqty from Currstock as C where c.rcvdate=stock.rcvdate)
Then 0
When @orderqty < (select Prvqty from Currstock C2 where c2.rcvdate=stock.rcvdate)
Then Stock.qty
Else (select Currqty from Currstock as C3 where C3.rcvdate=stock.rcvdate)
-@orderqty End
End
--
Delete from the stock where qty=0
---
Go
EXEC Removeqty 20
Go
---------------
Third, the use of "greedy algorithm" for order distribution
Copy Code code as follows:
-------also has a problem with how to use the smallest or largest warehouse to meet the order, assuming that the warehouse is not in order, you can button the desired order of choice to meet the order.
---Use the smallest warehouse to bring the minimum amount of work to the stevedores of the order, and to use the largest warehouse, you can clear more space in the warehouse
-------For example: for this set of data, you can use the (1,2,3,4,5,6,7) warehouse to use the goods in the (5,6,7,8) warehouse to meet the order requirements.
----This is the packing problem, it belongs to the NP complete system problem. For the general situation, this problem is difficult to solve, because to try all combinations, and if the amount of data is large,
----computers are hard to handle quickly.
---So with the "greedy algorithm", this algorithm is often calculated to be almost optimal. The core of this algorithm is to "bite the biggest mouthful" until it reaches or exceeds the target.
---
--1. The first trick is to insert some empty dumb warehouses into the table, and if you need to select up to n times, add n-1 to the dummy warehouse
Insert Stock
Select-1, ' 10561122 ', ' 1900-1-1 ', 0,0 Union
Select-2, ' 10561122 ', ' 1900-1-1 ', 0,0
--select-3, ' 1900-1-1 ', 0,0
----
Go
CREATE VIEW Pickcombos
As
SELECT DISTINCT (w1.qty+w2.qty+w3.qty) as Totalpick
, case when w1.id<0 then 0 else w1.id end as Bin1, W1.qty as Qty1,
Case when w2.id<0 then 0 else w2.id end as bin2,w2.qty as Qty2
, case when w3.id<0 then 0 else w3.id end as bin3, W3.qty as Qty3
From the stock W1,stock W2, the stock W3
where W1.id not in (w2.id,w3.id)
and W2.id not in (w1.id,w3.id)
and W1.qty>=w2.qty
and W2.qty>=w3.qty
----
---1. Use stored procedures to find a selection combination that satisfies or approaches a certain number
--------
Go
Create proc Overpick
@pickqty int
As
if (@pickqty >0)
Begin
Select @pickqty, Totalpick,bin1,qty1,bin2,qty2,bin3,qty3
From Pickcombos
where totalpick= (select MIN (Totalpick) from Pickcombos where totalpick>= @pickqty)
End
Go
EXEC Overpick 180
----------
SELECT * FROM Stock
drop TABLE Stock
Drop View LIFO
Drop View FIFO
Drop View Costfifo
Drop View Costlifo
Drop View Currstock
drop proc Overpick
drop proc Removeqty
Drop View Pickcombos
