库存管理报表查询

Question

现在有三张表： MaterialStore  原料库存表 
ms_ID  此表的主键 
m_ID    外键，对应原料表 
ms_Date  原料库存发生变化的日期 
ms_Worker  盘库人姓名 
ms_GoodNum    好品数量 
ms_BadNum    次品数量 
ms_IsCount    是否是盘库添加的记录 
ms_Re    备注 
这个表记录着原料库存变化的情况，即，如果库存减少或增加就添加一条记录 BuyMaterial  原料进库表 
bm_ID  此表的主键 
m_ID    外键，对应原料表 
bm_Code  进库单编号 
bm_Num    进库数量 
bm_UnitPrice 进库单价 
bm_TotalPrice  进库总价 
bm_Date    进库日期 
bm_IsDeleted  此单是否删除 
bm_StaffName    经办人 
bm_Invoice      发票编号 
此表记录原料进库信息，有原料进库则添加一条记录 SellMaterial    原料出库表 
sm_ID    此表的逐渐 
c_ID    外键，对应客户表 
m_ID    外键，对应原料表 
sm_Code  出库单号 
sm_Num    出库数量 
sm_UnitPrice  出库单价 
sm_TotalPrice  出库总价 
sm_Date      出库日期 
sm_IsDeleted  此单是否已删除 
sm_StaffName    经办人 
sm_IsOut    是否是为外加工原料 
此表记录的原料出库信息，有原料出库则添加一条记录 
现在，需要去做报表。 
给定某月，需要查询出，该月初的库存量、本月的进库量、本月的出库量、月末量、差额（月初库存+本月进库-本月出库由于丢失可能不等于月末量）。这个查询要怎么写？ 
小弟第一次做，请前辈指教。 
如果对表结构不顺眼的也可以提些建议。 
谢谢

Answer 1

贴数据
--看看这个例子  
    
  --测试数据   
  create   table   tb(單據編號   int,物料編號   varchar(10),供應商號   varchar(10),日期   datetime,數量   int,標記   int,類別   varchar(10))   
  insert   tb   select   1111111,'MMM1','VVV1','2004/9/1',500,   1,'入庫'   
  union   all   select   2222222,'MMM1','VVV1','2004/9/2',400,-1,'出庫'   
  union   all   select   3333333,'MMM1','VVV1','2004/9/8',300,   1,'入庫'   
  union   all   select   4444444,'MMM2','VVV2','2004/9/1',600,   1,'入庫'   
  union   all   select   5555555,'MMM2','VVV2','2004/9/3',500,-1,'出庫'   
  union   all   select   6666666,'MMM2','VVV2','2004/9/9',200,   1,'入庫'   
  go   
    
  --查询   
  select   單據編號,物料編號,供應商號,日期   
  ,入庫=sum(case   when   類別='入庫'   then   數量   else   0   end)   
  ,出庫=sum(case   when   類別='出庫'   then   數量   else   0   end)   
  ,結存=(select   sum(數量*標記)   from   tb   where   供應商號=a.供應商號   
  and   (日期<a.日期   or   日期=a.日期   and   單據編號<=a.單據編號))   
  from   tb   a   
  group   by   單據編號,物料編號,供應商號,日期   
  go   
    
  --删除测试   
  drop   table   tb   
    
  /*--测试结果   
    
  單據編號           物料編號       供應商號         日期                                           入庫       出庫         結存       
  -----------   ----------   ----------   ------------------------   ------   ------   ------   
  1111111           MMM1               VVV1               2004-09-01   00:00:00.000     500         0               500   
  2222222           MMM1               VVV1               2004-09-02   00:00:00.000     0             400           100   
  3333333           MMM1               VVV1               2004-09-08   00:00:00.000     300         0               400   
  4444444           MMM2               VVV2               2004-09-01   00:00:00.000     600         0               600   
  5555555           MMM2               VVV2               2004-09-03   00:00:00.000     0             500           100   
  6666666           MMM2               VVV2               2004-09-09   00:00:00.000     200         0               300   

Answer 2

谢谢。但和我的要求相差还是有些大。
我有三张表，这个只有一张表，比较好写。
主要是MaterialStore里的数据
MaterialStore的信息是某时刻的库存情况， 
比如，原始库存，材料A有100，材料B有200，材料C有300 
那MaterialStore表的数据有 
A 100  2009-1-1 
B 200  2009-1-1 
C 300  2009-1-1 
2009-10-1添加原料A 50 
则MaterialStore表的数据添加一条后变成 
A 100  2009-1-1 
B 200  2009-1-1 
C 300  2009-1-1 
A 150  2009-10-1 
BuyMaterial和SellMaterial是对进出库做的详细记录
怎么通过这张表查询某月所有原料的期初和期末数据？还要将进库表和出库表里对应的数据也加进来，既总进库量和总出库量。 

Answer 3

恩。因为第一次做这样的，前面的数据都是按照这样的表结构操作的。现在做最后的工作，就是报表了。
我给几个示例数据：
初始数据：
MaterialStore
m_ID   ms_Num    ms_Date
1       100       2009-9-1
2       150       2009-9-1
3       120       2009-9-1
此时BuyMaterial和SellMaterial还没有数据
9月10号添加1原料30
9月13号添加3原料50
9月15号2原料出库40
后三张表的数据：
BuyMaterial
m_ID    bm_Num   bm_Date
1       30        2009-9-10
3       50        2009-9-13
SellMaterial
m_ID    sm_Num   sm_Date
2        40       2009-9-15
MaterialStore
m_ID   ms_Num    ms_Date
1       100       2009-9-1
2       150       2009-9-1
3       120       2009-9-1
1       130       2009-9-10
3       170       2009-9-13
2       110       2009-9-15
要查询的结果
m_ID    lastNum      inNum    outNum     endNum
1        100          30        0          130
2        150          0         40         110
3        120          50        0          170
请大侠看一下。

Answer 4

declare @start_rq varchar(10),
        @end_rq varchar(10)
select @start_rq='2009-04-20',@end_rq='2009-04-22'update a set a.kcqc_shl=b.kcqc_shl,a.kcqc_je=b.kcqc_je,a.hwqc_shl=b.hwqc_shl,a.hwqc_je=b.hwqc_je,
             a.jcshl=b.jcshl,a.jcje=b.jcje,a.hwjcshl=b.hwjcsh,a.hwjcje=b.hwjcer
from #spinfo a(nolock),
(select a.spid,a.hw,a.jcshl,a.jcje,a.hwjcsh,a.hwjcer,a.jcshl-a.chkshl+a.rkshl as kcqc_shl,a.jcje-a.chkje+a.rkje as kcqc_je,
       a.hwjcsh-a.chkshl+a.rkshl as hwqc_shl,a.hwjcer-a.chkje+a.rkje as hwqc_je
   from splsk a(nolock),(select spid,hw,max(plh) as plh from splsk(nolock) group by spid,hw) b
   where a.plh=b.plh and a.spid=b.spid and a.hw=b.hw and a.rq>=@start_rq and a.rq<=@end_rq) b
where a.spid=b.spid and a.hw=b.hw你看！

Answer 5

if object_ID('BuyMaterial') IS NOT NULL DROP TABLE BuyMaterial
  create table BuyMaterial(m_ID  int,bm_Num int,    bm_Date varchar(10))
insert BuyMaterial 
 select
1,      30,        '2009-9-10' union all select
3,      50,        '2009-9-13' 
goif object_ID('SellMaterial') IS NOT NULL DROP TABLE SellMaterial
go
  create table SellMaterial(m_ID  int,sm_Num int,    sm_Date varchar(10))
insert SellMaterial 
 select
2,        40,      '2009-9-15' go
if object_ID('MaterialStore') IS NOT NULL DROP TABLE MaterialStore
  create table MaterialStore(m_ID  int,ms_Num int,    ms_Date varchar(10))
insert MaterialStore 
 select
1,      100,      '2009-9-1' union all select
2,      150,      '2009-9-1' union all select
3,      120,      '2009-9-1' union all select
1,      130,      '2009-9-10' union all select
3,      170,      '2009-9-13' union all select
2,      110,      '2009-9-15' 
select  X.m_ID,(select SUM(ms_num) from MaterialStore where m_ID=x.m_ID and ms_Date<x.ms_Date) as 期初,
              (select SUM(bm_num) from BuyMaterial where m_ID=x.m_ID) as 本期入库,
              (select SUM(sm_Num) from SellMaterial where m_ID=x.m_ID) as 本期出库,
              (select SUM(ms_num) from MaterialStore where m_ID=x.m_ID and ms_Date>x.ms_Date) as 期末结存
             -- (select SUM(a.ms_Num)+SUM(b.bm_Num)-sum(c.sm_Num) from MaterialStore a,BuyMaterial b,SellMaterial c
              --  where a.m_ID=b.m_ID and b.m_id=c.m_ID and a.m_ID=x.m_ID and a.ms_Date<x.ms_Date) as 本期入库
  from  MaterialStore x(nolock)
  group by X.m_ID,ms_Date
/*
m_ID 期初 本期入库 本期出库 期末结存
1 NULL 30 NULL 130
1 100 30 NULL NULL
2 NULL NULL 40 110
2 150 NULL 40 NULL
3 NULL 50 NULL 170
3 120 50 NULL NULL
*/

Answer 6

if object_ID('BuyMaterial') IS NOT NULL DROP TABLE BuyMaterial
  create table BuyMaterial(m_ID  int,bm_Num int,    bm_Date varchar(10))
insert BuyMaterial 
 select
1,      30,        '2009-9-10' union all select
3,      50,        '2009-9-13' 
goif object_ID('SellMaterial') IS NOT NULL DROP TABLE SellMaterial
go
  create table SellMaterial(m_ID  int,sm_Num int,    sm_Date varchar(10))
insert SellMaterial 
 select
2,        40,      '2009-9-15' go
if object_ID('MaterialStore') IS NOT NULL DROP TABLE MaterialStore
  create table MaterialStore(m_ID  int,ms_Num int,    ms_Date varchar(10))
insert MaterialStore 
 select
1,      100,      '2009-9-1' union all select
2,      150,      '2009-9-1' union all select
3,      120,      '2009-9-1' union all select
1,      130,      '2009-9-10' union all select
3,      170,      '2009-9-13' union all select
2,      110,      '2009-9-15' 
select  X.m_ID,(select SUM(ms_num) from MaterialStore where m_ID=x.m_ID and ms_Date<x.ms_Date) as 期初,
              (select SUM(bm_num) from BuyMaterial where m_ID=x.m_ID) as 本期入库,
              (select SUM(sm_Num) from SellMaterial where m_ID=x.m_ID) as 本期出库,
              (select SUM(ms_num) from MaterialStore where m_ID=x.m_ID and ms_Date>x.ms_Date) as 期末结存 into #a
             -- (select SUM(a.ms_Num)+SUM(b.bm_Num)-sum(c.sm_Num) from MaterialStore a,BuyMaterial b,SellMaterial c
              --  where a.m_ID=b.m_ID and b.m_id=c.m_ID and a.m_ID=x.m_ID and a.ms_Date<x.ms_Date) as 本期入库
  from  MaterialStore x(nolock)
  group by X.m_ID,ms_Dateselect a.m_id,a.期初,d.本期入库,d.本期出库,d.期末结存
from 
  (select * from #a where 期初 is not null)a
    left join (select * from #a where 期末结存 is not null) d on a.m_id=d.m_id
    
    /*
m_id 期初 本期入库 本期出库 期末结存
1 100 30 NULL 130
2 150 NULL 40 110
3 120 50 NULL 170
*/

Answer 7

declare @MaterialStore table(
ms_ID  int ,
m_ID   int,
ms_Date  datetime,
ms_Worker  varchar(20),
ms_GoodNum    int,
ms_BadNum    int,
ms_IsCount    int,
ms_Re    varchar(50)
)declare @BuyMaterial table(
bm_ID  int,
m_ID    int,
bm_Code  int,
bm_Num    int,
bm_UnitPrice int,
bm_TotalPrice  int,
bm_Date    datetime,
bm_IsDeleted  int,
bm_StaffName    varchar(20),
bm_Invoice      int
)declare @SellMaterial table(
sm_ID    int,
c_ID    int,
m_ID    int,
sm_Code  int,
sm_Num    int,
sm_UnitPrice  int,
sm_TotalPrice  int,
sm_Date      datetime,
sm_IsDeleted  int,
sm_StaffName    int,
sm_IsOut   int
 )insert into @MaterialStore (ms_ID,ms_GoodNum,ms_Date) select 1,      100,      '2009-9-1'
union all select 2,      150,      '2009-9-1'
union all select 3,      120,      '2009-9-1'
union all select 1,      130,      '2009-9-10'
union all select 3,      170,      '2009-9-13'
union all select 2,      110,      '2009-9-15' 
 --select ms_ID,ms_GoodNum,ms_Date from @MaterialStore
 
 insert into @BuyMaterial (m_ID,bm_Num,bm_Date) select 1,      30,        '2009-9-10'
union all select 3,      50,        '2009-9-13'--select m_ID,bm_Num,bm_Date from  @BuyMaterial insert into @SellMaterial (m_ID,    sm_Num,  sm_Date) select 2,        40,      '2009-9-15'
 
 --select m_ID,    sm_Num,  sm_Date from @SellMaterial 
 
 select ms_ID 编号,
 (select ms_GoodNum from @MaterialStore m where day(ms_Date)=1 and m.ms_ID=t.ms_ID )  期初, 
 (select isnull(min(bm_Num),0) from @BuyMaterial where m_ID=t.ms_ID )  本期入库,
 (select isnull(min(sm_Num),0) from @SellMaterial where m_ID=t.ms_ID ) 本期出库,
 ( select ms_GoodNum from @MaterialStore where ms_Date= (select max(ms_Date) from @MaterialStore m where m.ms_ID=t.ms_ID )) 期末结存
 from @MaterialStore t
 group by ms_ID
 
编号 期初 本期入库 本期出库 期末结存
1 100 30 0 130
2 150 0 40 110
3 120 50 0 170