sql交叉表查询期初库存（高手看看）

[nn360697589]

（见附件）
这样的一张表，其中dep_id代表代理商的代号，Channels_Name代表代理商的名称，其中OperType代表操作的类型（0代表缴纳，1代表支取退还）,Amount代表操作的金额。

需求是输入一段时间如：2009-02-25，2009-02-27查出以下这样一张表

代理商名称 编码 缴纳 支取 最后余额 期初金额（前一天的最后余额）
Dld 321 1100 100 1000
Dld2 322 1000 300 700


各位大哥大姐帮忙写一个存储过程，查出这张表（最好不用游标）

[nn360697589]

我自己经过考虑得到答案如下，但是当期初金额（amount_begin）没有的时候就没有数据了怎么办？
select b.Channels_Name ,b.dep_id ,
isnull(b.amount_begin,0) as amount_begin,
sum(isnull(a.amount_in,0)) as amount_in,
sum(isnull(a.amount_out,0)) as amount_out,
sum(isnull(a.amount_correct,0)) as amount_correct,
isnull(b.amount_begin,0) + sum(isnull(a.amount_in,0)) + sum(isnull(a.amount_out,0)) + sum(isnull(a.amount_correct,0)) as amount_last
from
(select Channels_Name,dep_id,sum(isnull(amount,0)) as amount_begin
from AgentAcct_History
where Oper_datetime <'2009-02-26'
group by Channels_Name,dep_id ) b
left outer join
(select Channels_Name ,dep_id ,
case when OperType = '0' then Amount else 0 end as Amount_in,
case when OperType = '1' then Amount else 0 end as Amount_out,
case when Opertype = '2' then amount else 0 end as amount_correct
from AgentAcct_History
where Oper_datetime between '2009-02-26' and '2009-02-27' + ' 24:00:00'
) a
on b.dep_id = a.dep_id
group by b.Channels_Name ,b.dep_id ,amount_begin

[dawugui]

您访问的资源不存在

[htl258_Tony]

同上

[nn360697589]

==

[nn360697589]

首先谢谢你们!表如下：
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentAcct_History]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AgentAcct_History]
GO

CREATE TABLE [dbo].[AgentAcct_History] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Dep_Id] [int] NOT NULL ,
[Channels_Dm] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Channels_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Oper_datetime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Oper_PersonId] [int] NOT NULL ,
[Amount] [decimal](18, 2) NOT NULL ,
[OperType] [int] NOT NULL ,
[isPrint] [tinyint] NULL ,
[Print_datetime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[AcctLsh] [char] (7) COLLATE Chinese_PRC_CI_AS NULL ,
[DsfLsh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[AmountJc] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

[dawugui]

请给出表结构，测试数据，具体算法和结果，谢谢。

[nn360697589]

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AgentAcct_History]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AgentAcct_History]
GO
CREATE TABLE [dbo].[AgentAcct_History] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Dep_Id] [int] NOT NULL ,
[Channels_Dm] [varchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Channels_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Oper_datetime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Oper_PersonId] [int] NOT NULL ,
[Amount] [decimal](18, 2) NOT NULL ,
[OperType] [int] NOT NULL ,
[isPrint] [tinyint] NULL ,
[Print_datetime] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[AcctLsh] [char] (7) COLLATE Chinese_PRC_CI_AS NULL ,
[DsfLsh] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[AmountJc] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

insert into AgentAcct_History values (dld,2009-02-25,1322,1000,0,1322001,1000)
insert into AgentAcct_History values (dld2,2009-02-25,1322,500,0,1322001,500)
insert into AgentAcct_History values (dld,2009-02-26,1322,100,0,1322001,1100)
insert into AgentAcct_History values (dld,2009-02-26,1322,-50,1,1322001,1050)
insert into AgentAcct_History values (dld,2009-02-27,1322,-50,1,1322001,1000)
insert into AgentAcct_History values (dld2,2009-02-27,1322,500,0,1322001,1000)
insert into AgentAcct_History values (dld2,2009-02-27,1322,-300,1,1322001,700)
insert into AgentAcct_History values (dld2,2009-02-27,1322,100,0,1322001,800)
这样的一张表，其中dep_id代表代理商的代号，Channels_Name代表代理商的名称，其中OperType代表操作的类型（0代表缴纳，1代表支取退还）,Amount代表操作的金额,AmountJc代表当天代理商的交易结存。

需求是输入一段时间如：2009-02-25，2009-02-27查出以下这样一张表

代理商名称 编码 缴纳 支取 最后余额（期初金额+今天的交易） 期初金额（前一天的最后余额）
Dld 321 1100 100 -1000 ？ ？
Dld2 322 1000 300 -700 ？ ？


各位大哥大姐帮忙写一个存储过程，查出这张表（最好不用游标）

写一个存储过程如exec hosend’2005,2007’

（初次进入本论坛，分不多，谢谢大家帮忙，我在线等各位高手的消息，本人感激万分！）