表SaleBill
有cCusCode,dDate,cDepCode,cPsnCode,fMoney三个字段
表DebtAge
有iArea,iFirst,iEnd三个字段(iArea区间号,iFirst起始时间,iEnd结束时间）
要求建一个查询，要求按客户，按部门，按业务员统计出在每一个区间iArea中的金额。
该怎么写？
尽量简单。
---------------------------------------------------------------
两个表没有任何的联系，不知道：（
---------------------------------------------------------------
～～～没有测试～～～
create procedure proc1
@cCusCode bit =null
,@cDepCode bit=null
,@cPsnCode bit=null
as
declare @cgroup varchar(100)
declare @cmain varchar(2000)
declare @corder varchar(100)
--declare @where varchar(2000
declare @sql varchar(4000)
--没有考虑显示的字段，可以自行考虑
set @cmain='select a.iArea,sum(b.fMoney) from DebtAge a left out join SaleBill b
on b.dDate>=a.iFirst and b.dDate<a.End'
set @cgroup = ' group by a.iArea '
set @corder = ' order by a.iArea '
--set @where = ' where 1 = 1 '
if @cCucSode = 1
@cgroup = @cgroup + 'b.cCusCode = '
if @cDepCode = 1
@cgroup = @cgroup + 'b.cDepCode = '
if @cPsnCode = 1
@cgroup = @cgroup + 'b.cPsnCode = '
set @sql = @cmain + @cgroup + @corder
exec(@sql)
go
---------------------------------------------------------------
部门：
select B.iArea, A.cDepCode, sum(A.fMoney) from SaleBill as A, DebtAge as B
group by A.cDepCode, B.iArea
where dDate is between B.iFirst and B.iEnd
其他类似。
---------------------------------------------------------------
先将数据查询出来，然后再交叉制表就是。
---------------------------------------------------------------
举个例子吧！
以前的贴应该有的。
---------------------------------------------------------------
你的结果怎么从原始数据弄出来的，说清楚之间的关系！
---------------------------------------------------------------
--建表
create table table1 (cCusCode varchar(10),dDate datetime, cDepCode varchar(10), cPsnCode varchar(10), fMoney int)
insert table1 select '001','2002-8-1' ,'001','001',100
union all select '001','2002-8-15','001','002',230
union all select '001','2002-9-1' ,'001','002',200
union all select '002','2002-9-2' ,'002','002',300
create table table2(iArea int, iFirst int, iEnd int)
insert table2 select 1 , 1 , 30
union all select 2 , 31 , 60
union all select 3 , 61 , 90
---用动态SQL语句：
declare @sql nvarchar(4000)
declare @d datetime
set @d=getdate()
set @sql='select cCuscode '
select @sql=@sql+',sum(case when datediff(dd,dDate,'''+convert(varchar(10),@d,120)+''') between '
+cast(iFirst as varchar(10))+' and '+cast(iEnd as varchar(10))+
' then fMoney else 0 end) as '''+cast(iFirst as varchar(10))+'-'+cast(iEnd as varchar(10))+'''' from table2
set @sql=@sql+' from table1 group by cCusCode'
exec(@sql)
--result:
cCuscode 1-30 31-60 61-90
---------- ----------- ----------- -----------
001 200 330 0
002 300 0 0