某物流系统的常用表如下
--产品表
create table PRODUCT(
ID Integer Not Null , --流水号
NAME VarChar(60) , --名称
NUMCODE VarChar(20) , --数字码
PYCODE VarChar(60) , --拼音码
ISWAS Smallint Default 0 --废码 0(有效) 1(作废)
);
--业务单据父表
create table BILLP(
ID Integer Not Null , --流水号
BILLNO VarChar(20) , --单据号
INSTORE Smallint , --调入仓库 1(仓库一) 2(仓库二) 3(仓库三) 4(仓库4)
OUTSTORE Smallint , --调出仓库 1(仓库一) 2(仓库二) 3(仓库三) 4(仓库4)
BILLSTATE Smallint , --单据状态 10(录入状态) 20(已确认) 30(已审核) 40(作废)
BILLTIME DateTime , --填表时间
ISWAS Smallint Default 0 --废码 0(有效) 1(作废)
);
//注:如<“调入仓库”为2,“调出仓库”为3>时,“仓库二”的库存增加,“仓库三”的库存减少--业务单据子表
create table BILLC(
ID Integer Not Null , --父表ID FK-BillP-ID
SEQID Integer Not Null , --行号
ProdID Integer , --产品ID
QTY Decimal(12,4) , --调拨数量
ISWAS Smallint Default 0 --废码 0(有效) 1(作废)
); 请写一SQL语句,根据<填表时间>统计某一时间段(2007-11-01至 2007-11-30),各个产品在“仓库二”的上期库存数,本期出仓数,本期入仓数,本期结余库存数。(只统计<单据状态>为 “已确认” 或 “已审核”的单据)
格式如: 产品名 上期库存数 本期出仓数 本期入仓数 本期结余库存数
A产品 50 800 950 200
--产品表
create table PRODUCT(
ID Integer Not Null , --流水号
NAME VarChar(60) , --名称
NUMCODE VarChar(20) , --数字码
PYCODE VarChar(60) , --拼音码
ISWAS Smallint Default 0 --废码 0(有效) 1(作废)
);
--业务单据父表
create table BILLP(
ID Integer Not Null , --流水号
BILLNO VarChar(20) , --单据号
INSTORE Smallint , --调入仓库 1(仓库一) 2(仓库二) 3(仓库三) 4(仓库4)
OUTSTORE Smallint , --调出仓库 1(仓库一) 2(仓库二) 3(仓库三) 4(仓库4)
BILLSTATE Smallint , --单据状态 10(录入状态) 20(已确认) 30(已审核) 40(作废)
BILLTIME DateTime , --填表时间
ISWAS Smallint Default 0 --废码 0(有效) 1(作废)
);
//注:如<“调入仓库”为2,“调出仓库”为3>时,“仓库二”的库存增加,“仓库三”的库存减少--业务单据子表
create table BILLC(
ID Integer Not Null , --父表ID FK-BillP-ID
SEQID Integer Not Null , --行号
ProdID Integer , --产品ID
QTY Decimal(12,4) , --调拨数量
ISWAS Smallint Default 0 --废码 0(有效) 1(作废)
); 请写一SQL语句,根据<填表时间>统计某一时间段(2007-11-01至 2007-11-30),各个产品在“仓库二”的上期库存数,本期出仓数,本期入仓数,本期结余库存数。(只统计<单据状态>为 “已确认” 或 “已审核”的单据)
格式如: 产品名 上期库存数 本期出仓数 本期入仓数 本期结余库存数
A产品 50 800 950 200
select @dt1 = '2007-11-01', @dt2 = '2007-11-30'select c.NAME,
上期库存数 = sum(case when a.BILLTIME<@dt1 then case when INSTORE=2 then b.Qty when OUTSTORE=2 then -b.Qty end else 0 end),
本期出仓数 = sum(case when a.BILLTIME>=@dt1 and OUTSTORE=2 then b.Qty else 0 end),
本期入仓数 = sum(case when a.BILLTIME>=@dt1 and INSTORE=2 then b.Qty else 0 end),
本期结余库存数 = sum(case when INSTORE=2 then b.Qty when OUTSTORE=2 then -b.Qty else 0 end)
from BILLP a
join BILLC b on a.ID=b.ID
join PRODUCT c on b.ProdID=c.ID
where a.BILLTIME<@dt2+1 and a.BILLSTATE in (20,30)-- and (INSTORE=2 or OUTSTORE=2)
group by a.NAME
"根据<填表时间>统计某一时间段(2007-11-01至 2007-11-30)",楼主这样设计有问题,上期取07年10,07年10取07年9月,倒,如开帐时间是07年3月,哪别人公司07年3月以前的数据从天上掉下来!!!
上期库存数:应该2007-11-01这前的各产品的库存数。所以要还原到从前,
2007-11-01至 2007-11-30之内的库存,出库则加,入库则减,
本期出仓数:应该是2007-11-30此时的产品的库存数
还有不太明白!贴出数据来分析吧
declare @dt1 datetime, @dt2 datetime
select @dt1 = '2007-11-01', @dt2 = '2007-11-30'select c.NAME,
上期库存数 = sum(case when a.BILLTIME=@id2 then b.Qty when a.BILLTIME <@dt2 then case when INSTORE=2 then -b.Qty when OUTSTORE=2 then b.Qty end else 0 end),
本期出仓数 = sum(case when OUTSTORE=2 then b.Qty else 0 end),
本期入仓数 = sum(case when INSTORE=2 then b.Qty else 0 end),
本期结余库存数 = sum(case when INSTORE=2 then b.Qty when OUTSTORE=2 then -b.Qty else 0 end)
from BILLP a
join BILLC b on a.ID=b.ID
join PRODUCT c on b.ProdID=c.ID
where a.BILLTIME between @id1 and @dt2 and a.BILLSTATE in (20,30)-- and (INSTORE=2 or OUTSTORE=2)
group by c.NAME
declare @dt1 datetime, @dt2 datetime
select @dt1 = '2007-11-01', @dt2 = '2007-11-30'select c.NAME,
上期库存数 = sum(case when a.BILLTIME=@dt2 then b.Qty when a.BILLTIME <@dt2 then case when INSTORE=2 then -b.Qty when OUTSTORE=2 then b.Qty end else 0 end),
本期出仓数 = sum(case when OUTSTORE=2 then b.Qty else 0 end),
本期入仓数 = sum(case when INSTORE=2 then b.Qty else 0 end),
本期结余库存数 = sum(case when INSTORE=2 then b.Qty when OUTSTORE=2 then -b.Qty else 0 end)
from BILLP a
join BILLC b on a.ID=b.ID
join PRODUCT c on b.ProdID=c.ID
where a.BILLTIME between @dt1 and @dt2 and a.BILLSTATE in (20,30)-- and (INSTORE=2 or OUTSTORE=2)
group by c.NAME