这个存储过程如何写？

Question

有一个表，字段：机构号，增加量，日期；

我需要得到这样的表：字段：机构号，实际的量（绝对量），日期；

存储过程怎么做？输入 起始日期，结束日期；假设知道某日的绝对量。

 

比如2010-12-1 绝对量是100
t1：
3|2010-12-2
4|2010-12-3
2|2010-12-4
那需要得到比如2010-12-2至2010-12-4的查询表为：
103|2010-12-2
107|2010-12-3
109|2010-12-4

Answer 1

select 机构号,实际的量=(select sum(增加量) from ta where 机构号 = a.机构号 and datediff(d,a.日期,日期)<=0),日期

from ta a 

---

More: blog.csdn.net/happyflystone

Answer 2

use Tempdb
go
--> -->
 
if not object_id(N'T') is null
  drop table T
Go
Create table T([Qty] int,[Date] Datetime)
Insert T
select 10,'2010-12-1' union all
select 3,'2010-12-2' union all
select 4,'2010-12-3' union all
select 2,'2010-12-4'
Go
IF OBJECT_ID('P1') IS NOT NULL
  DROP PROCEDURE P1
go
CREATE PROCEDURE P1
(
  @Qty INT,
  @StartDate DATETIME,
  @EndDate DATETIME
)
AS
Select
  @Qty+(SELECT SUM([Qty]) FROM T WHERE [Date]<=a.[Date] AND [Date] BETWEEN @StartDate AND @EndDate)as [实际的量],
  a.Date AS 日期
from T AS a
WHERE [Date] BETWEEN @StartDate AND @EndDate
go

EXEC P1
  @Qty=100,--2010-12-01 实际的量
  @StartDate='2010-12-2',
  @EndDate='2010-12-4'
/*
实际的量  日期
103  2010-12-02 00:00:00.000
107  2010-12-03 00:00:00.000
109  2010-12-04 00:00:00.000
*/

---

ROY WU(吳熹)