报表的时候，有一个关于名称，期初余额，本期发生，期末余额……的报表，请问这样那位有写过这样的SQL,能不能借鉴以下。
------解决方案--------------------
要根据你的表结构来写
楼主最好贴出你的表结构，附上少许几条纪录，然后写出你想要的结果
------解决方案--------------------
确实笼统了些
我的个人感觉是要让关系清晰了
别的就好办了
------解决方案--------------------
请参考邹建的方法
--结存表
CREATE TABLE Stocks(Item varchar(10),Period int,Balance int)
INSERT Stocks SELECT 'aa ',200501,100
UNION ALL SELECT 'cc ',200501,100
--明细账数据
CREATE TABLE tb(
ID int IDENTITY PRIMARY KEY,
Item varchar(10), --产品编号
Quantity int, --交易数量
Flag bit, --交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)
Date datetime) --交易日期
INSERT tb SELECT 'aa ',100,1, '2005-1-1 '
UNION ALL SELECT 'aa ',90 ,1, '2005-2-1 '
UNION ALL SELECT 'aa ',55 ,0, '2005-2-1 '
UNION ALL SELECT 'aa ',-10,1, '2005-2-2 '
UNION ALL SELECT 'aa ',-5 ,0, '2005-2-3 '
UNION ALL SELECT 'aa ',200,1, '2005-2-2 '
UNION ALL SELECT 'aa ',90 ,1, '2005-2-1 '
UNION ALL SELECT 'bb ',95 ,1, '2005-2-2 '
UNION ALL SELECT 'bb ',65 ,0, '2005-2-3 '
UNION ALL SELECT 'bb ',-15,1, '2005-2-5 '
UNION ALL SELECT 'bb ',-20,0, '2005-2-5 '
UNION ALL SELECT 'bb ',100,1, '2005-2-7 '
UNION ALL SELECT 'cc ',100,1, '2005-1-7 '
GO
--查询时间段定义
DECLARE @dt1 datetime,@dt2 datetime
SELECT @dt1= '2005-2-2 ',@dt2= '2005-2-10 '
--查询
--期初库存年月及计算期初数的开始时间)
DECLARE @Period int,@dt datetime
SELECT @Period=CONVERT(CHAR(6),DATEADD(Month,-1,@dt1),112),
@dt=DATEADD(Day,1-Day(@dt1),@dt1)
--查询期初库存
SELECT Item=ISNULL(a.Item,b.Item),
Date=ISNULL(b.Date,CONVERT(char(10),@dt1,120)),
Opening=ISNULL(a.Balance,0)+ISNULL(b.Opening,0),
[IN]=ISNULL(b.[IN],0),
[IN_Retrun]=ISNULL(b.[IN_Retrun],0),
[OUT]=ISNULL(b.[OUT],0),
[OUT_Return]=ISNULL(b.[OUT_Return],0),
Balance=ISNULL(a.Balance,0)+ISNULL(b.Opening,0)+ISNULL(b.Amount,0)
FROM(
--期初数
SELECT Item,Balance FROM Stocks WHERE [email protected]
)a FULL JOIN(
--统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)
SELECT Item,
Date=CONVERT(char(10),@dt1,120),
Opening=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END),
[IN]=0,
[IN_Retrun]=0,
[OUT]=0,
[OUT_Return]=0,
Amount=0
FROM tb a
WHERE Date> [email protected] AND Date <@dt1
AND NOT EXISTS(
SELECT * FROM tb WHERE Item=a.Item AND Date> @dt1 AND Date <DATEADD(Day,1,@dt2))
GROUP BY Item
UNION ALL
--指定时间段内有交易发生的数据
SELECT Item,
Date=CONVERT(char(10),Date,120),
Opening=(SELECT SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
FROM tb WHERE Item=a.Item AND Date> [email protected] AND Date <MIN(a.Date)),
[IN]=SUM(CASE WHEN Flag=1 AND Quantity> 0 THEN Quantity END),
[IN_Retrun]=SUM(CASE WHEN Flag=1 AND Quantity <0 THEN -Quantity END),
[OUT]=SUM(CASE WHEN Flag=0 AND Quantity> 0 THEN Quantity END),
[OUT_Return]=SUM(CASE WHEN Flag=0 AND Quantity <0 THEN -Quantity END),
Amount=SUM(CASE WHEN Flag=1 THEN Quantity ELSE -Quantity END)
FROM tb a
WHERE Date> [email protected] AND Date <DATEADD(Day,1,@dt2)
GROUP BY CONVERT(char(10),Date,120),Item
)b ON a.Item=b.Item
ORDER BY Item,Date
/*--结果
Item Date Opening IN IN_Retrun OUT OUT_Return Balance
---------- ---------------- -------------- ----------- ---------------- ----------- ------------------- -----------