SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析

代码如下:

---在仓储管理中经常会碰到的一个问题

一、关于LIFO与FIFO的简单说明
---FIFO: First in, First out.先进先出。
---LIFO: Last in, First out.后进先出。

--如货物A:本月1日购买10件,单价10元/件,3日购买20件,单价15元/件;10日购买10件,单价8元/件。
--本月15日发货35件。

--按FIFO先进先出,就是先购入的存货先发出,所以,先发1日进货的10件,再发3日进货的20件,最后发10日进货的5件,发出成本共为:10*10+20*15+5*8=440元。
--按LIFO后进先出,就是后购入的存货先发出,所以,先发10日进货的10件,再发3日进货的20件,最后发1日进货的5件,发出成本共为:10*8+20*15+5*10=430元

二、示例


代码如下:

--------
Create table stock
(Id int not null primary key,
articleno varchar(20) not null,
rcvdate datetime not null,
qty int not null,
unitprice money 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
----此时如果在2011-8-8卖出300件产品,那么应该如何计算库存销售的价值呢?
----1使用当前的替换成本,2011-8-8时每件产品的成本为8,就是说你这300件产品,成本价值为2400
----2使用当前的平均成本单价,一共有420,总成本为6530,平均每件的成本为15.55
----1.LIFO (后进先出)
----2011-8-8 110 *8
----2011-7-7 75*17
----2011-6-6 65*30
----2011-5-5 50*10
-----总成本为 4605
-----2.FIFO(先进先出)
---- '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
----总成本为5480

---成本视图
create view costLIFO
as
select unitprice from stock
where rcvdate= (select MAX(rcvdate) from stock)
go
create view costFIFO
as
select sum(unitprice*qty)/SUM(qty) as unitprice from stock

go
-----找出满足订单的、足够存货的最近日期。如果运气好的话,某一天的库存数量正好与订单要求的数字完全一样
-----就可以将总成本作为答案返回。如果订单止的数量比库存的多,什么也不返回。如果某一天的库存数量比订单数量多
---则看一下当前的单价,乘以多出来的数量,并减去它。
---下面这些查询和视图只是告诉我们库存商品的库存价值,注意,这些查询与视图并没有实际从库存中向外发货。
create view LIFO
as
select s1.rcvdate,s1.unitprice,sum(s2.qty) as qty,sum(s2.qty*s2.unitprice) as totalcost
from stock s1 ,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 stock s1 ,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
-----
-----在发货之后,实时更新库存表
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 end) as CurrQty
from stock s1 ,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 stock where qty=0
---
go
exec RemoveQty 20
go
---------------

三、使用“贪婪算法”进行订单配货

代码如下:

-------还有一个问题,如何使用空间最小或最大的仓库中的货物来满足订单,假设仓库不是顺序排列,你可以按钮希望的顺序任意选择满足订单。
---使用最小的仓库可以为订单的装卸工人带来最小的工作量,使用最大的仓库,则可以在仓库中清理出更多的空间
-------例如:对于这组数据,你可以使用(1,2,3,4,5,6,7)号仓库也可以使用(5,6,7,8)号仓库中的货物来满足订单的需求。
----这个就是装箱问题,它属于NP完全系统问题。对于一般情况来说,这种问题很难解决,因为要尝试所有的组合情况,而且如果数据量大的话,
----计算机也很难很快处理。
---所以有了“贪婪算法”,这个算法算出来的常常是近乎最优的。这个算法的核心就是“咬最大的一口”直到达到或超越目标。
---
--1. 第一个技巧,要在表中插入一些空的哑仓库,如果你最多需要n次挑选,则增加n-1个哑仓库
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 stock w1,stock w2, 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.使用存储过程来找出满足或接近某一数量的挑选组合
--------
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

(0)

相关推荐

  • SQL语句练习实例之一——找出最近的两次晋升日期与工资额

    复制代码 代码如下: --程序员们在编写一个雇员报表,他们需要得到每个雇员当前及历史工资状态的信息, --以便生成报表.报表需要显示每个人的晋升日期和工资数目. --如果将每条工资信息都放在结果集的一行中,并让宿主程序去格式化它. --应用程序的程序员都是一帮懒人,他们需要在每个雇员的一行上得到当前 --和历史工资信息.这样就可以写一个非常简单的循环语句. ---示例: create table salaries ( name nvarchar(50) not null, sal_date da

  • SQL语句练习实例之七 剔除不需要的记录行

    复制代码 代码如下: --相信大家肯定经常会把数据导入到数据库中,但是可能会有些记录行的所有列的数据是null,这为null的数据是我们不需要 --现在需要一个简单的查询来剔除掉这些为null的记录行. --假设表名为 emplyees --方法1. ---先把数据导入到数据库 ---其次: select * from sys.columns where object_id =(select object_id from sys.objects where name='EMPLYEEs') --

  • SQL语句练习实例之六 人事系统中的缺勤(休假)统计

    复制代码 代码如下: ---这是一个人事系统中的示例,要求记录一下员工的缺勤情况 ---1.要在表中记录一下缺勤计分,是对经常缺勤者的一种处罚性计分 ---规则: ---1.如果员工在一年内的缺勤计分达到50,就会可以解雇该员工. ---2.如果员工缺勤连续超过一天,就视为长病假,这时,第二天,第三天及以后的天数都不会统计该员工的缺勤计分 ----这些天也不算为缺勤. create table absence ( empId int not null, absenceDate datetime

  • SQL语句练习实例之二——找出销售冠军

    复制代码 代码如下: --销售冠军 --问题:在公司中,老板走进来,要一张每个地区销量前3名的销售额与销售员的报表 --- create table salesdetail ( Area int not null, Saler nvarchar(20) not null, SalerId int not null, Sales money not null ) insert salesdetail select 1,'张三',15,3000 union select 1,'赵一',16,3500

  • SQL语句练习实例之三——平均销售等待时间

    复制代码 代码如下: ---1.平均销售等待时间 ---有一张Sales表,其中有销售日期与顾客两列,现在要求使用一条SQL语句实现计算 --每个顾客的两次购买之间的平均天数 --假设:在同一个人在一天中不会购买两次 create table sales ( custname varchar(10) not null, saledate datetime not null ) go insert sales select '张三','2010-1-1' union select '张三','20

  • SQL语句练习实例之四 找出促销活动中销售额最高的职员

    复制代码 代码如下: ---找出促销活动中销售额最高的职员 ---你刚在一家服装销售公司中找到了一份工作,此时经理要求你根据数据库中的两张表得到促销活动销售额最高的销售员 ---1.一张是促销活动表 ---2.一张是销售客列表 create table Promotions ( activity nvarchar(30), sdate datetime, edate datetime ) insert Promotions select '五一促销活动','2011-5-1','2011-5-7

  • SQL语句练习实例之五 WMS系统中的关于LIFO或FIFO的问题分析

    复制代码 代码如下: ---在仓储管理中经常会碰到的一个问题 一.关于LIFO与FIFO的简单说明 ---FIFO: First in, First out.先进先出. ---LIFO: Last in, First out.后进先出. --如货物A:本月1日购买10件,单价10元/件,3日购买20件,单价15元/件:10日购买10件,单价8元/件. --本月15日发货35件. --按FIFO先进先出,就是先购入的存货先发出,所以,先发1日进货的10件,再发3日进货的20件,最后发10日进货的5

  • Mybatis基于注解形式的sql语句生成实例代码

    对其做了些优化,但此种sql生成方式仅适用于复杂程度不高的sql,所以实用性不是很高,仅仅是写着玩的,知道点mybatis的注解形式的使用方式,可能以后会逐渐完善起来.第一次写博客,写的简单点. package com.bob.config.mvc.mybatis; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retenti

  • Java使用Statement接口执行SQL语句操作实例分析

    本文实例讲述了Java使用Statement接口执行SQL语句操作的方法.分享给大家供大家参考,具体如下: Statement执行SQL语句: 1. 对数据库的曾删改操作时,使用stmt.executeUpdate(sql)  执行给定 SQL 语句,分别为 insert .update.delete. 2. 对数据库做查询时,直接使用 stmt.executeQuery(sql),返回结果可以为一个resultSet结果集. 首先做一些准备工作: ①对要进行操作的数据库表进行封装,比如说我的数

  • PHP实现的构造sql语句类实例

    本文实例讲述了PHP实现的构造sql语句类.分享给大家供大家参考,具体如下: /** * @package Database Class * @author injection (mail:injection.mail@gmail.com) * @version 1.0 */ @ini_set( 'display_errors',0 ); class DataBase{ private $mDb_host,$mAb_user,$mAb_pwd,$mConn_No; function DataBa

  • 实例解析Android系统中的ContentProvider组件用法

    ContentProvider为Android四大组件之一,主要用来应用程序之间的数据共享,也就是说一个应用程序用ContentProvider将自己的数据暴露出来,其他应用程序通过ContentResolver来对其暴露出来的数据进行增删改查. ContenProvider与ContentResolver之间的对话同过Uri(通用资源标识符),一个不恰当的比喻就好像浏览器要显示一个网页要有一个东西发送请求,这相当于ContentResolver,你要拿东西就要知道去哪里拿,你就得知道服务器的域

随机推荐