select
日期, 物料, 收入, 发出,
结存 = (select sum(收入)-sum(结存) from 表 where 日期 <= a.日期 and 物料=a.物料)
from 表 a
解决方案 »
- JDBC Driver连接sql server时,出现“Error establishing socket.”的问题。
- "alter table mytable add bmlx char(2) not null "这句有错吗?
- 如何用SQL语句列出树型结构
- 怎样才能做到SQL server和自己的程序一起发布?(怎样才能做到SQL Server集成到VB安装程序)在别的电脑上不用独立在安装SQL server.谢谢
- SQL数据库特别问题
- 如何设计相关的表?请高手指点!!
- 2005vs连接2000sql(服务器)的问题
- 如何查出表中某一列多项间的分隔符是什么
- 你们碰到过吗?
- 一个挺麻烦的问题!(求高手解决)
- 请教:怎么分割一张表中的数据
- SQL SERVER 代理的问题.
日期, 物料, 收入 = 0, 发出 = 0,
结存 = (SELECT SUM(收入)-SUM(结存) FROM 表 WHERE 日期 < A.日期 AND 物料=A.物料)
FROM(
SELECT
日期 = CONVERT(datetime, CONVERT(char(6), MIN(日期), 112) + '01'),
物料
FROM 表
GROUP BY 物料
)A
UNION ALL
SELECT
日期, 物料, 收入, 发出,
结存 = (SELECT SUM(收入)-SUM(结存) FROM 表 WHERE 日期 <= A.日期 AND 物料=A.物料)
FROM 表 A
ORDER BY 日期
return float
declare @i float
select top 1 @i = 結存 from 表 where 物料 = @物料
return @i
select 結存 = (sum(收入 - 发出 ) + fn(物料) ) from 表 where 日期 <= 你指定的日期 group by 物料
return float
declare @i float
select top 1 @i = 結存 from 表 where 物料 = @物料 order by 日期
return @i
select 結存 = (sum(收入 - 发出 ) + fn(物料) ) from 表 where 日期 <= 你指定的日期 group by 物料
---------入库\领料单--------
select fyear=year(b.fdate),
fperiod=month(b.fdate),
fdate = convert(varchar(10),b.fdate,120),
b.ftrantype,
b.fbillno,
fstockid=case when b.ftrantype=1 or b.ftrantype=2 or b.ftrantype=10 or b.ftrantype=40 then a.fdcstockid else a.fscstockid end,
a.fitemid ,
fincome =
case when b.ftrantype in (1,2,10,40) then a.fqty
else 0
end,
fsendout =
case when b.ftrantype in (21,24,29,43) then a.fqty
else 0
end,
fcashonhand = 0
from icstockbillentry a, icstockbill b
where ((a.fdcstockid between 3824 and 4658) or (a.fscstockid between 3824 and 4658)) and (fitemid between 1638 and 1638)and (year(b.fdate) between 2006 and 2007)
and (month(b.fdate) between 01 and 12) and a.finterid = b.finterid and b.ftrantype<>100 and b.ftrantype<>41union all--------调拨单
select fyear=year(b.fdate),fperiod=month(b.fdate),
fdate = convert(varchar(10),b.fdate,120),
b.ftrantype,
b.fbillno,a.fdcstockid fstockid,a.fitemid,
fincome=a.fqty,
fsendout=0,
fcashonhand=0
from icstockbillentry a left join icstockbill b on a.finterid=b.finterid where ((a.fdcstockid between 3824 and 3824) or (a.fscstockid between 3824 and 4658)) and (fitemid between 1638 and 1638)and (year(b.fdate) between 2006 and 2007)
and (month(b.fdate) between 01 and 12) and b.ftrantype=41
union all
select fyear=year(b.fdate),fperiod=month(b.fdate),
fdate = convert(varchar(10),b.fdate,120),
b.ftrantype,
b.fbillno,a.fdcstockid fstockid,a.fitemid,
fincome=0,
fsendout=a.fqty,
fcashonhand=0
from icstockbillentry a left join icstockbill b on a.finterid=b.finterid where ((a.fdcstockid between 3824 and 3824) or (a.fscstockid between 3824 and 4658)) and (fitemid between 1638 and 1638)and (year(b.fdate) between 2006 and 2007)
and (month(b.fdate) between 01 and 12) and b.ftrantype=41
--------期初库存数据--------
select fyear,
fperiod,
fdate=cast(fyear as varchar(4))+'-'+right('00'+cast(fperiod as varchar(2)),2)+'-01' ,
ftrantype = 90 ,
fbillno = ' ' ,
fstockid ,
fitemid ,
fincome = 0 ,
fsendout = 0 ,
fcashonhand = fbegqty
from icinvbal where (fstockid between 3824 and 4658) and (fitemid between 1638 and 1638) and (fyear between 2006 and 2007)
and (fperiod between 01 and 12)
union all
模式來處理代碼量較多,但簡潔快速有效.