计算销售成本(希望大家帮忙)

Question

数据结构及建表语句如下:GoodsArchives(存货档案)
ItemNo  --货号
Purchaseprice --基准价  
branch_no--店号ItemNo  Purchaseprice   branch_no
---------------------------------------
0290287   15.00000   001
0290288   33.00000   001
0290289   33.00000   001
0290289   33.00000   002
0290288   33.00000   002
Warehousing (仓存表)
ItemNo --货号   
Beginning  --期初数  
branch_no --店号ItemNo    Beginning    branch_no
-----------------------------------------
0290287    20.00000 001
0290287    12.00000 001
0290288    9.00000 001
0290289    10.00000 001
0290289    15.00000 002
0290288    9.00000 002
JinhuoDan (进货单)ItemNo--货号   
Amount--进货数量         
Purchasepric --进价  
branch_no --店号   
JinhuoDate--进货日期
ItemNo   Amount         Purchasepric   branch_no    JinhuoDate
------------------------------------------------------------------------------
0290287  10.00000 16.50000 001    2009-11-04 00:00:00.000
0290288  20.00000 34.00000 001    2009-11-07 00:00:00.000
0290289  5.00000 37.00000 001    2009-11-09 00:00:00.000
0290289  6.00000 35.00000 002    2009-11-14 00:00:00.000
0290288  8.00000 38.00000 002    2009-11-20 00:00:00.000
SalesTable (销售单)
StockNO --货号   
Amount --销售数量            
branch_no --店号      
SalesDate --销售日期
StockNO    Amount             branch_no       SalesDate
----------------------------------------------------------------
0290287    2.00000           001  2009-11-03 00:00:00.000
0290287    1.00000         001  2009-11-05 00:00:00.000
0290288    3.00000         001  2009-11-07 00:00:00.000
0290289    5.00000         001  2009-11-07 00:00:00.000
0290289    6.00000         002  2009-11-14 00:00:00.000
0290288    8.00000         002  2009-11-23 00:00:00.000
/*if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GoodsArchives]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GoodsArchives]
GOCREATE TABLE [dbo].[GoodsArchives] (
[ItemNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Purchaseprice] [decimal](18, 5) NULL ,
[branch_no] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GOinsert GoodsArchives 
 select '0290287',15.00000,'001' union   all
 select '0290288',33.00000,'001' union   all
 select '0290289',33.00000,'001' union all
 select '0290289',33.00000,'002' union all
 select '0290288',33.00000,'002'if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[JinhuoDan]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[JinhuoDan]
GO
CREATE TABLE [dbo].[JinhuoDan] (
[ItemNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Amount] [decimal](18, 5) NULL ,
[Purchaseprice] [decimal](18, 5) NULL, 
[branch_no] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
        [JinhuoDate] [datetime] NULL 
) ON [PRIMARY]
GO
insert JinhuoDan 
 select '0290287',10.00000,16.5,'001','2009-11-4' union   all
 select '0290288',20,34,'001','2009-11-7' union   all
 select '0290289',5,37.00000,'001','2009-11-9' union all
 select '0290289',6,35.00000,'002','2009-11-14' union all
 select '0290288',8,38.00000,'002','2009-11-20'if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SalesTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SalesTable]
GOCREATE TABLE [dbo].[SalesTable] (
[StockNO] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Amount] [decimal](18, 5) NULL ,
        [branch_no] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[SalesDate] [datetime] NULL ,) ON [PRIMARY]
GOinsert SalesTable 
 select '0290287',2,'001','2009-11-3' union   all
  select '0290287',1,'001','2009-11-5' union   all
 select '0290288',3,'001','2009-11-7' union   all
 select '0290289',5,'001','2009-11-7' union all
 select '0290289',6,'002','2009-11-14' union all
 select '0290288',8,'002','2009-11-23'if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Warehousing]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Warehousing]
GO
CREATE TABLE [dbo].[Warehousing] (
[ItemNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
        [Beginning] [decimal](18, 5) NULL ,
[branch_no] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]
GOinsert Warehousing 
 select '0290287',20,'001' union   all
 select '0290287',12,'001' union   all
 select '0290288',9,'001' union   all
 select '0290289',10,'001' union all
 select '0290289',15,'002' union all
 select '0290288',9,'002'*/
需求:按移动平均法得出每次销售的成本(注:期初没有进价的,以存货档案的基准价为进价)

Answer 1

GoodsArchives(存货档案)
ItemNo  --货号
Purchaseprice --基准价  
branch_no--店号ItemNo  Purchaseprice   branch_no
---------------------------------------
0290287      15.00000      001
0290288      33.00000      001
0290289      33.00000      001
0290289      33.00000      002
0290288      33.00000      002
Warehousing (仓存表)
ItemNo --货号   
Beginning  --期初数  
branch_no --店号ItemNo    Beginning    branch_no
-----------------------------------------
0290287       20.00000    001
0290287       12.00000    001
0290288       9.00000    001
0290289       10.00000    001
0290289       15.00000    002
0290288       9.00000    002
JinhuoDan (进货单)ItemNo--货号   
Amount--进货数量         
Purchasepric --进价  
branch_no --店号   
JinhuoDate--进货日期
ItemNo   Amount         Purchasepric   branch_no    JinhuoDate
------------------------------------------------------------------------------
0290287     10.00000    16.50000    001       2009-11-04 00:00:00.000
0290288     20.00000    34.00000    001       2009-11-07 00:00:00.000
0290289     5.00000    37.00000    001       2009-11-09 00:00:00.000
0290289     6.00000    35.00000    002       2009-11-14 00:00:00.000
0290288     8.00000    38.00000    002       2009-11-20 00:00:00.000
SalesTable (销售单)
StockNO --货号   
Amount --销售数量            
branch_no --店号      
SalesDate --销售日期
StockNO    Amount             branch_no       SalesDate
----------------------------------------------------------------
0290287       2.00000              001     2009-11-03 00:00:00.000
0290287       1.00000            001     2009-11-05 00:00:00.000
0290288       3.00000            001     2009-11-07 00:00:00.000
0290289       5.00000            001     2009-11-07 00:00:00.000
0290289       6.00000            002     2009-11-14 00:00:00.000
0290288       8.00000            002     2009-11-23 00:00:00.000
--需求:按移动平均法得出每次销售的成本(注:期初没有进价的,以存货档案的基准价为进价)

Answer 2

在另一貼也已回復，樓主定議一個庫存初始時間就可統計了
set nocount on ;CREATE TABLE [dbo].[GoodsArchives] (
    [ItemNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
        [Beginning][decimal](18, 5) NULL ,
    [Purchaseprice] [decimal](18, 5) NULL 
) ON [PRIMARY]insert [GoodsArchives] select '01',20,10
union all select '02',10,5
union all select '03',0,0CREATE TABLE [dbo].[JinhuoDan] (
    [ItemNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [Amount] [decimal](18, 5) NULL ,
    [Purchaseprice] [decimal](18, 5) NULL ,
    [JinhuoDate] [datetime] NULL ,
) ON [PRIMARY]
insert [JinhuoDan] select '01',5,11,'2009-11-1'
union all select '03',10,13,'2009-11-7'CREATE TABLE [dbo].[SalesTable] (
    [ItemNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
    [Amount] [decimal](18, 5) NULL ,
    [Retail] [decimal](18, 5) NULL ,
    [SalesDate] [datetime] NULL 
) ON [PRIMARY]
insert [SalesTable] select '01',10,13,'2009-10-20'
union all select '01',12,13,'2009-11-3'
union all select '02',5,7,'2009-11-4'
union all select '03',2,15,'2009-11-7';with JinHuo
as
(
select * from [JinhuoDan]
union all
select *,[JinhuoDate]='2009-10-01' --設置庫存初始時間
from [GoodsArchives] where Beginning>0
),JinHuo2
as
(
select *,(select Sum(Amount)  from JinHuo where [ItemNo]=a.[ItemNo] and  [JinhuoDate]<=a.[JinhuoDate]) as TotalAmount from JinHuo a
),SalesOrder
as
(
select *,(select sum(Amount) from [SalesTable] where [ItemNo]=a.[ItemNo] and  [SalesDate]<=a.[SalesDate]) as TotalAmount from [SalesTable] a
)
select 
[货号]=b.[ItemNo],
[銷量]=(case when b.TotalAmount<a.TotalAmount then b.TotalAmount else a.TotalAmount end)-
   (case when b.TotalAmount-b.Amount<a.TotalAmount-a.Amount then a.TotalAmount-a.Amount else b.TotalAmount-b.Amount end),
[售價]=b.[Retail],
[售價成本]=a.[Purchaseprice],
[利润]= (b.[Retail]-a.[Purchaseprice])*
((case when b.TotalAmount<a.TotalAmount then b.TotalAmount else a.TotalAmount end)-
(case when b.TotalAmount-b.Amount<a.TotalAmount-a.Amount then a.TotalAmount-a.Amount else b.TotalAmount-b.Amount end)),
[日期]=b.[SalesDate]
from JinHuo2 a
inner join SalesOrder b on a.[ItemNo]=b.[ItemNo] and a.TotalAmount-a.Amount<b.TotalAmount and b.TotalAmount-b.Amount<a.TotalAmount
order by 1,2 desc货号                                                 銷量                                      售價                                      售價成本                                    利润                                      日期
-------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -----------------------
01                                                 10.00000                                13.00000                                10.00000                                30.000000                               2009-10-20 00:00:00.000
01                                                 10.00000                                13.00000                                10.00000                                30.000000                               2009-11-03 00:00:00.000
01                                                 2.00000                                 13.00000                                11.00000                                4.000000                                2009-11-03 00:00:00.000
02                                                 5.00000                                 7.00000                                 5.00000                                 10.000000                               2009-11-04 00:00:00.000
03                                                 2.00000                                 15.00000                                13.00000                                4.000000                                2009-11-07 00:00:00.000

Answer 3

以上用了SQL2005以上版本的CTE，如果是SQL2000改為臨時表實現