CREATE TABLE [dbo].[frontfront](
[No] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Price] [int] NULL,
[Amount] [int] NULL,
[InDate] [datetime] NULL
) ON [PRIMARY]
goinsert into 先进先出 select '001',8, 10,'2007-04-10'
union all select '001', 10, 20, '2007-04-12'
union all select '001', 12, 30, '2007-04-15'
union all select '002', 5, 10, '2007-01-15'
gocreate procedure frontfront_getPrice
(@no nvarchar(50),@amount int)
as
--declare @amount int
--declare @no int
--set @amount=25
--set @no=001
declare @date datetime
select top 1 @date=indate from frontfront f1 where [no]=@no
and (select sum(amount) from frontfront where [no]=@no and indate<=f1.indate)>@amount order by Indate asc
select [no],price,amount=(case indate when @date then @amount-(select sum(amount) from frontfront where [no]=@no and indate<@date) else amount end),inDate from frontfront where [no]=@no and indate<=@date
go
exec frontfront_getPrice '001',25结果如下:
no price amount inDate
------------------------------- ----------- ----------- -----------------------
001 8 10 2007-04-10 00:00:00.000
001 10 15 2007-04-12 00:00:00.000
[No] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[Price] [int] NULL,
[Amount] [int] NULL,
[InDate] [datetime] NULL
) ON [PRIMARY]
goinsert into 先进先出 select '001',8, 10,'2007-04-10'
union all select '001', 10, 20, '2007-04-12'
union all select '001', 12, 30, '2007-04-15'
union all select '002', 5, 10, '2007-01-15'
gocreate procedure frontfront_getPrice
(@no nvarchar(50),@amount int)
as
--declare @amount int
--declare @no int
--set @amount=25
--set @no=001
declare @date datetime
select top 1 @date=indate from frontfront f1 where [no]=@no
and (select sum(amount) from frontfront where [no]=@no and indate<=f1.indate)>@amount order by Indate asc
select [no],price,amount=(case indate when @date then @amount-(select sum(amount) from frontfront where [no]=@no and indate<@date) else amount end),inDate from frontfront where [no]=@no and indate<=@date
go
exec frontfront_getPrice '001',25结果如下:
no price amount inDate
------------------------------- ----------- ----------- -----------------------
001 8 10 2007-04-10 00:00:00.000
001 10 15 2007-04-12 00:00:00.000
解决方案 »
- 请大家帮忙:dbgrid显示求和字段问题!!!
- select 语句 where 条件不能查询韩文
- MS-SQL2000修改了用户密码,同时把windows验证改成了系统和windows验证,sql Agent就无法启动了,已改成了
- 哪里來的空記錄
- sql 题- 写个存储过程,判断传入的参数是否是正确的IP地址。怎么写?
- SQL 日期如何相减
- 不截取字符串前面和后面的空格
- 急!sql2000连接的怪问题
- 好不容易下了个sql server单机版.可是文件是.iso不能打开(有500M),用什么打开的??
- 客户端长时间不用,连接不上sql server
- 关于计算先进先出库存成本问题
- 数据库转移服务器后,提示存储过程找不到!
declare @a table(进货单 int ,供应商_id varchar(2),商品_id varchar(1),定价 decimal(9,3),进货数 int,
折扣 decimal(5,3),jh_time datetime)
insert into @a
select '1','A1','B',10,100,0.6,'2006-01-01'union all
select '2','A2','B',10,50,0.7,'2006-02-01'union all
select '3','A1','B',10,100,0.6,'2006-03-01'
--销售表
declare @b table (销售单_id varchar(2),商品_id varchar(1),定价 decimal(9,3),销售数量 int,
折扣 decimal(5,3),jh_time datetime)
insert into @b
select '11','B',10,80,0.9,'2006-01-02'union all
select '22','B',10,60,0.8,'2006-02-02'union all
select '33','B',10,40,1,'2006-03-02'select 销售单_id,tb.商品_id,
[sales]=
sum(((case when tb.num>ta.num then ta.num else tb.num end)-
(case when ta.num-ta.进货数<tb.num-tb.销售数量 then tb.num-tb.销售数量 else ta.num-ta.进货数 end))*ta.折扣)from
(select *,
num=(select isnull(sum(进货数),0) from @a where 商品_id=a.商品_id and 进货单!>a.进货单)
from @a a)ta
inner join
(select *,
num=(select isnull(sum(销售数量),0) from @b where 商品_id=b.商品_id and 销售单_id!>b.销售单_id)
from @b b)tb
on ta.商品_id=tb.商品_id
where
tb.num-销售数量<ta.num and ta.num-进货数<tb.num
group by 销售单_id,tb.商品_id
order by 销售单_id(3 行受影响)(3 行受影响)销售单_id 商品_id sales
------ ----- ---------------------------------------
11 B 48.000
22 B 40.000
33 B 25.000(3 行受影响)
id 商品代码 数量 价格 日期 类别
1 001 10 8 2007-03-01 采购
2 001 8 12 2007-03-10 采购
3 001 12 6 2007-03-15 采购
4 001 2 16 2007-04-05 销售
5 001 18 16 2007-04-20 销售
我的目的是按照先进先出方法统计4月份销售数量和销售成本,销售成本应该是10*8元
+8*12元+2*6元=188元,销售数量为20个,请问各位大虾怎么写sql语句