又來麻煩大家啦， 想找个简便点的计算方法， 大虾帮下

Question

表1有6個字段
单号    自动编号         需求日期      需求数量     ONTIME数量 OVERTIME数量
A  1 2011-1-3   10
A  2 2011-1-7   15
A  3 2011-1-15   20表2有2个资料
日期 生产数
2011-1-1   7
2011-1-5   12
2011-1-8   11
2011-1-14   15要求计算出结果
单号    自动编号         需求日期       需求数量     ONTIME数量 OVERTIME数量
A  1 2011-1-3   10 7           3
A  1 2011-1-7   15            9           6
A  1 2011-1-15   20            20想找个快捷点的办法， 游标的话试了下感觉效率很低。。

Answer 1

http://topic.csdn.net/u/20111110/11/2f3a0106-8085-4d43-981a-d105ac9b92b8.html
看看这个帖子

Answer 2

小F好久不见了。。
ONTIME数量是这样算的
不是1月3号有10PCS的需求， 然后1月1号有生产7个， 1月5号有生产12个
所以ONTIME的是7PCS， 剩余3PCS就是OVERTIME的

Answer 3

--库存先进先出简单例子：create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int,        --入库数量
c int,        --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select  'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select  'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select  'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select  'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select  'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select  'A',510,0,'2008-03-01'
gocreate proc wsp
@name varchar(50),--商品名称
@cost int         --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name 
if(@spare>=@cost)
begin
    --根据入库日期采用先进先出原则对货物的库存进行处理
    update t set c=
    case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
    then a.j 
    else 
        case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0 
        else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) 
        end 
    end
    from t a where name=@name and j!=c 
end
else
    raiserror('库存不足',16,1)    
    return 
go
--测试：exec wsp @name='A',@cost=180
select * from t
--drop table t
--drop proc wsp

Answer 4

这例子里我觉得return好像没用
没在begin end里
就相当于所有情况都走到最后的return
但是这时过程已经完了

Answer 5

create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',10,null,null union all
select 'A',2,'2011-1-7',15,null,null union all
select 'A',3,'2011-1-15',20,null,null
gocreate table tb2(
日期 datetime,
生产数 int
)
insert into tb2
select '2011-1-1',7 union all
select '2011-1-5',12 union all
select '2011-1-8',11 union all
select '2011-1-14',15
go
/*
要求计算出结果
单号    自动编号         需求日期       需求数量     ONTIME数量 OVERTIME数量
A     1        2011-1-3      10        7           3
A     1        2011-1-7      15            9           6
A     1        2011-1-15      20            20
*/select 单号,自动编号,需求日期,需求数量,
ONTIME数量=isnull((select sum(生产数) from tb2 where 日期<=t.需求日期),0)
 - isnull((select sum(需求数量) from tb1 where 需求日期<t.需求日期),0),
OVERTIME数量=isnull((select sum(需求数量) from tb1 where 需求日期<=t.需求日期),0)
-isnull((select sum(生产数) from tb2 where 日期<=t.需求日期),0)
from tb1 tdrop table tb1,tb2/**************单号         自动编号        需求日期                    需求数量        ONTIME数量    OVERTIME数量
---------- ----------- ----------------------- ----------- ----------- -----------
A          1           2011-01-03 00:00:00.000 10          7           3
A          2           2011-01-07 00:00:00.000 15          9           6
A          3           2011-01-15 00:00:00.000 20          20          0(3 行受影响)
可能还是需要改。

Answer 6

create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',10,null,null union all
select 'A',2,'2011-1-7',15,null,null union all
select 'A',3,'2011-1-15',20,null,null union all
select 'b',1,'2011-1-15',10,null,null union all
select 'b',2,'2011-1-15',20,null,null
gocreate table tb2(
单号 varchar(10),
日期 datetime,
生产数 int
)
insert into tb2
select 'A','2011-1-1',7 union all
select 'A','2011-1-5',12 union all
select 'A','2011-1-8',11 union all
select 'b','2011-1-14',15
go
select 单号,自动编号,需求日期,需求数量,
    ONTIME数量=isnull((select sum(生产数) from tb2 where tb2.单号=t.单号 and 日期<=t.需求日期),0)
             - isnull((select sum(需求数量) from tb1 where tb1.单号=t.单号 and  需求日期<t.需求日期),0),
    OVERTIME数量=isnull((select sum(需求数量) from tb1 where tb1.单号=t.单号 and  需求日期<=t.需求日期),0)
                -isnull((select sum(生产数) from tb2 where tb2.单号=t.单号 and  日期<=t.需求日期),0)
from tb1 tdrop table tb1,tb2试了下2个单，可是好像就有问题了， 该怎么改呢

Answer 7

create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',10,null,null union all
select 'A',2,'2011-1-7',15,null,null union all
select 'A',3,'2011-1-15',20,null,null union all
select 'b',1,'2011-1-15',10,null,null union all
select 'b',2,'2011-1-15',20,null,null
gocreate table tb2(
单号 varchar(10),
日期 datetime,
生产数 int
)
insert into tb2
select 'A','2011-1-1',7 union all
select 'A','2011-1-5',12 union all
select 'A','2011-1-8',11 union all
select 'b','2011-1-14',15
go
select 单号,自动编号,需求日期,需求数量,
    ONTIME数量=isnull((select sum(生产数) from tb2 where tb2.单号=t.单号 and 日期<=t.需求日期),0)
             - isnull((select sum(需求数量) from tb1 where tb1.单号=t.单号 and 需求日期<t.需求日期),0),
    OVERTIME数量=isnull((select sum(需求数量) from tb1 where tb1.单号=t.单号 and  需求日期<=t.需求日期 and 自动编号<=t.自动编号),0)
                -isnull((select sum(生产数) from tb2 where tb2.单号=t.单号 and  日期<=t.需求日期),0)
from tb1 tdrop table tb1,tb2/***********************单号         自动编号        需求日期                    需求数量        ONTIME数量    OVERTIME数量
---------- ----------- ----------------------- ----------- ----------- -----------
A          1           2011-01-03 00:00:00.000 10          7           3
A          2           2011-01-07 00:00:00.000 15          9           6
A          3           2011-01-15 00:00:00.000 20          5           15
b          1           2011-01-15 00:00:00.000 10          15          -5
b          2           2011-01-15 00:00:00.000 20          15          15(5 行受影响)
???

Answer 8

create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',10,null,null union all
select 'A',2,'2011-1-7',15,null,null union all
select 'A',3,'2011-1-15',20,null,null
gocreate table tb2(
日期 datetime,
生产数 int
)
insert into tb2
select '2011-1-1',7 union all
select '2011-1-5',12 union all
select '2011-1-8',11 union all
select '2011-1-14',15
goselect m.单号,m.自动编号,m.需求日期,m.需求数量 ,
       ONTIME数量 = (select sum(n.生产数) from tb2 n where n.日期 <= m.需求日期) - isnull((select sum(需求数量) from tb1 t where t.需求日期 < m.需求日期 ),0),
       OVERTIME数量  = isnull((select sum(需求数量) from tb1 t where t.需求日期 <= m.需求日期 ),0) - (select sum(n.生产数) from tb2 n where n.日期 <= m.需求日期)
from tb1 m
order by m.单号,m.自动编号,m.需求日期drop table tb1 , tb2/*
单号         自动编号        需求日期                                                   需求数量        ONTIME数量    OVERTIME数量  
---------- ----------- ------------------------------------------------------ ----------- ----------- ----------- 
A          1           2011-01-03 00:00:00.000                                10          7           3
A          2           2011-01-07 00:00:00.000                                15          9           6
A          3           2011-01-15 00:00:00.000                                20          20          0（所影响的行数为 3 行）*/如果是这个需求,tb2单是不是增加个字段,单号?这样两表才能对应起来?

Answer 9

嗯是的， 第二个生产数的表，要加个字段
create table tb2(
单号 varchar(10),
日期 datetime,
生产数 int
)
insert into tb2
select 'A','2011-1-1',7 union all
select 'A','2011-1-5',12 union all
select 'A','2011-1-8',11 union all
select 'b','2011-1-14',15 union all
select 'c','2011-1-14',150 union all
select 'c','2011-1-20',90
go类似这样

Answer 10

create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',10,null,null union all
select 'A',2,'2011-1-7',15,null,null union all
select 'A',3,'2011-1-15',20,null,null union all
select 'b',1,'2011-1-15',10,null,null union all
select 'b',2,'2011-1-15',20,null,null
gocreate table tb2(
单号 varchar(10),
日期 datetime,
生产数 int
)
insert into tb2
select 'A','2011-1-1',7 union all
select 'A','2011-1-5',12 union all
select 'A','2011-1-8',11 union all
select 'b','2011-1-14',15
go
select m.单号,m.自动编号,m.需求日期,m.需求数量 ,
       ONTIME数量 = (select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期) - isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 < m.需求日期 ),0),
       OVERTIME数量  = isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 <= m.需求日期 ),0) - (select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期)
from tb1 m
order by m.单号,m.自动编号,m.需求日期drop table tb1 , tb2/*
单号         自动编号        需求日期                                                   需求数量        ONTIME数量    OVERTIME数量  
---------- ----------- ------------------------------------------------------ ----------- ----------- ----------- 
A          1           2011-01-03 00:00:00.000                                10          7           3
A          2           2011-01-07 00:00:00.000                                15          9           6
A          3           2011-01-15 00:00:00.000                                20          5           15
b          1           2011-01-15 00:00:00.000                                10          15          15
b          2           2011-01-15 00:00:00.000                                20          15          15（所影响的行数为 5 行）
*/

Answer 11

刚仔细研究了下， 发现不用循环游标可能真的不行
因为如果生产数超过了， 或者不足 都需要特殊去判断， 唉头疼~~
create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',10,null,null union all
select 'A',2,'2011-1-7',15,null,null union all
select 'A',3,'2011-1-15',20,null,null union all
select 'b',1,'2011-1-15',10,null,null union all
select 'b',2,'2011-1-15',20,null,null
gocreate table tb2(
单号 varchar(10),
日期 datetime,
生产数 int
)
insert into tb2
select 'A','2011-1-1',7 union all
select 'A','2011-1-5',12 union all
select 'A','2011-1-8',11 union all
select 'b','2011-1-14',15
go
select m.单号,m.自动编号,m.需求日期,m.需求数量 ,
       ONTIME数量 = (select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期) - isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 < m.需求日期 ),0),
       OVERTIME数量  = isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 <= m.需求日期 ),0) - (select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期)
from tb1 m
order by m.单号,m.自动编号,m.需求日期drop table tb1 , tb2/*
单号         自动编号        需求日期                    需求数量        ONTIME数量    OVERTIME数量
---------- ----------- ----------------------- ----------- ----------- -----------
A          1           2011-01-03 00:00:00.000 10          7           3
A          2           2011-01-07 00:00:00.000 15          9           6
A          3           2011-01-15 00:00:00.000 20          5           15
b          1           2011-01-15 00:00:00.000 10          15          15
b          2           2011-01-15 00:00:00.000 20          15          15）
*/

Answer 12

如果你还有这个需求,用case when判断一下即可.
例如:
case when (select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期) > isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 < m.需求日期 ),0) then ...
else ...
end

Answer 13

其实主要就这些情况啦， 多张单的已经考虑啦生产数有可能会超过或者少于表1中的数。
如果有超过或者少于的情况， 按照刚才算法， 就有可能导致ONTIME数和OVERTIME的数计算出问题。
可能需要特殊去怎么判断。例如
如果A单有
Lot#1  10PCS
Lot#2  20PCS
然后A单的生产数为5PCS的时候，或者为40PCS的时候， 
就可能导致算出来的数为负数或者大于每Lot#的数量create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',10,null,null union all
select 'A',2,'2011-1-7',15,null,null union all
select 'A',3,'2011-1-15',20,null,null union all
select 'b',1,'2011-1-15',10,null,null union all
select 'b',2,'2011-1-15',20,null,null
gocreate table tb2(
单号 varchar(10),
日期 datetime,
生产数 int
)
insert into tb2
select 'A','2011-1-1',7 union all
select 'A','2011-1-5',12 union all
select 'A','2011-1-8',11 union all
select 'b','2011-1-14',15
go
select m.单号,m.自动编号,m.需求日期,m.需求数量 ,
       ONTIME数量 = (select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期) - isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 < m.需求日期 ),0),
       OVERTIME数量  = isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 <= m.需求日期 ),0) - (select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期)
from tb1 m
order by m.单号,m.自动编号,m.需求日期drop table tb1 , tb2/*
单号         自动编号        需求日期                    需求数量        ONTIME数量    OVERTIME数量
---------- ----------- ----------------------- ----------- ----------- -----------
A          1           2011-01-03 00:00:00.000 10          7           3
A          2           2011-01-07 00:00:00.000 15          9           6
A          3           2011-01-15 00:00:00.000 20          5           15
b          1           2011-01-15 00:00:00.000 10          15          15
b          2           2011-01-15 00:00:00.000 20          15          15
*/

Answer 14

解決了, 雖然不是最优方案， 感觉并循环会好些
先算出ONTIME数量， 然后在用CTE去计算出OVERTIME 的数量
如果直接去算OVERTIME的话，可能会有很多判断， 实在头疼了~
create table tb1(
单号 varchar(10),
自动编号 int,
需求日期 datetime,
需求数量 int,
ONTIME数量 int,
剩余数量 int,
OVERTIME数量 int
)
insert into tb1
select 'A',1,'2011-1-3',20,0,0,0 union all
select 'A',2,'2011-1-7',15,0,0,0 union all
select 'A',3,'2011-1-15',20,0,0,0 union all
select 'b',1,'2011-1-11',20,0,0,0 union all
select 'b',2,'2011-1-15',10,0,0,0 union all
select 'c',2,'2011-1-15',10,0,0,0
gocreate table tb2(
单号 varchar(10),
日期 datetime,
生产数 int
)
insert into tb2
select 'A','2011-1-1',7 union all
select 'A','2011-1-5',12 union all
select 'A','2011-1-18',11 union all
select 'b','2011-1-8',5 union all
select 'b','2011-1-14',18 union all
select 'c','2011-1-14',7
go
SELECT * FROM TB1
SELECT * FROM TB2UPDATE M
SET M.ONTIME数量 = (CASE
WHEN ISNULL((select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期),0)>isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 < m.需求日期 ),0)
THEN ISNULL((select sum(n.生产数) from tb2 n where n.单号 = m.单号 and n.日期 <= m.需求日期),0)-isnull((select sum(需求数量) from tb1 t where t.单号 = m.单号 and t.需求日期 < m.需求日期 ),0)
ELSE 0
 END)
FROM tb1 mUPDATE tb1
SET 剩余数量 = 需求数量 - ONTIME数量--按照SALES ORDER + CFM DATE +COUNTER排序分配FG STOCK
;WITH t AS 
(
    SELECT rn = ROW_NUMBER() OVER (ORDER BY t1.单号 ASC, 需求日期 ASC, t1.自动编号 ASC),*
    FROM   tb1 t1
),t1 AS
(
    SELECT *, tmpsum = (
SELECT SUM(剩余数量)
FROM   t
WHERE  单号 = a.单号 AND rn <= a.rn
    )
    FROM  t a
)UPDATE a
SET a.OVERTIME数量 = (CASE 
WHEN 剩余数量 -(a.tmpsum -b.生产数) <= 0 THEN 0
WHEN a.tmpsum - b.生产数 <= 0 THEN 剩余数量
ELSE 剩余数量 -(a.tmpsum -b.生产数)
 END)
FROM t1 a INNER JOIN (
SELECT T1.单号, T1.生产数-ISNULL(T2.ONTIME数量,'') AS 生产数
FROM (
SELECT 单号, sum(生产数) as 生产数
FROM   tb2
GROUP BY 单号
  ) AS T1 LEFT JOIN (
SELECT 单号, sum(ONTIME数量) as ONTIME数量
FROM   tb1
GROUP BY 单号
     ) AS T2 ON T1.单号 = T2.单号
 ) b ON  a.单号 = b.单号SELECT 单号, 自动编号, 需求日期, ONTIME数量, OVERTIME数量
FROM TB1drop table tb1 , tb2