--建立测试环境
create table TAB_JNGZ
(
ID int identity,
MSDM char(10),
FDATE datetime,
TDATE datetime,
JDATE datetime,
JE float,
FS char(10)
)insert into TAB_JNGZ values('A','20030101','20030331','20030110',20000,'月付')
insert into TAB_JNGZ values('B','20030201','20030430','20030210',15000,'月付')
insert into TAB_JNGZ values('C','20030105','20030704','20030110',50000,'季付')
insert into TAB_JNGZ values('A','20030401','20030630','20030405',55000,'季付')select * from tab_jngz--创建临时表
create table #ttt
(
ID int identity,
MSDM char(10),
SJ datetime,
JE float
)--获取数据
declare @cnt int
set @cnt=0
while(1=1)
begin
insert into #ttt(msdm,sj,je)
(select MSDM,(case FS when '月付' then dateadd(month,1*@cnt,JDATE)
when '季付' then dateadd(month,3*@cnt,JDATE)
when '半年付' then dateadd(month,6*@cnt,JDATE)
when '年付' then dateadd(year,1*@cnt,JDATE) end), JE
from tab_jngz
where (case FS when '月付' then dateadd(month,1*@cnt,JDATE)
when '季付' then dateadd(month,3*@cnt,JDATE)
when '半年付' then dateadd(month,6*@cnt,JDATE)
when '年付' then dateadd(year,1*@cnt,JDATE) end)<TDATE) if @@rowcount=0 break
select @cnt=@cnt+1
endselect * from #ttt--删除测试环境
drop table #ttt
drop table tab_jngz
create table TAB_JNGZ
(
ID int identity,
MSDM char(10),
FDATE datetime,
TDATE datetime,
JDATE datetime,
JE float,
FS char(10)
)insert into TAB_JNGZ values('A','20030101','20030331','20030110',20000,'月付')
insert into TAB_JNGZ values('B','20030201','20030430','20030210',15000,'月付')
insert into TAB_JNGZ values('C','20030105','20030704','20030110',50000,'季付')
insert into TAB_JNGZ values('A','20030401','20030630','20030405',55000,'季付')select * from tab_jngz--创建临时表
create table #ttt
(
ID int identity,
MSDM char(10),
SJ datetime,
JE float
)--获取数据
declare @cnt int
set @cnt=0
while(1=1)
begin
insert into #ttt(msdm,sj,je)
(select MSDM,(case FS when '月付' then dateadd(month,1*@cnt,JDATE)
when '季付' then dateadd(month,3*@cnt,JDATE)
when '半年付' then dateadd(month,6*@cnt,JDATE)
when '年付' then dateadd(year,1*@cnt,JDATE) end), JE
from tab_jngz
where (case FS when '月付' then dateadd(month,1*@cnt,JDATE)
when '季付' then dateadd(month,3*@cnt,JDATE)
when '半年付' then dateadd(month,6*@cnt,JDATE)
when '年付' then dateadd(year,1*@cnt,JDATE) end)<TDATE) if @@rowcount=0 break
select @cnt=@cnt+1
endselect * from #ttt--删除测试环境
drop table #ttt
drop table tab_jngz
create table #tab_jngz
(
ID int identity,
MSDM char(10),
FDATE datetime,
TDATE datetime,
JDATE datetime,
JE float,
FS char(10)
)insert #tab_jngz values('A','20030101','20030331','20030110',20000,'月付')
insert #tab_jngz values('B','20030201','20030430','20030210',15000,'月付')
insert #tab_jngz values('C','20030105','20030704','20030110',50000,'季付')
insert #tab_jngz values('A','20030401','20030630','20030405',55000,'季付')
select MSDM,(case FS when '月付' then dateadd(month,1*rowid,JDATE)
when '季付' then dateadd(month,3*rowid,JDATE)
when '半年付' then dateadd(month,6*rowid,JDATE)
when '年付' then dateadd(year,1*rowid,JDATE) end), JE
from #tab_jngz,(select (select count(*) from sysobjects where id<a.id) rowid from sysobjects a) b
where (case FS when '月付' then dateadd(month,1*rowid,JDATE)
when '季付' then dateadd(month,3*rowid,JDATE)
when '半年付' then dateadd(month,6*rowid,JDATE)
when '年付' then dateadd(year,1*rowid,JDATE) end)<TDATE and msdm='a'
--删除测试环境
drop table #tab_jngz
@fdate datetime, --查询的开始时间
@tdate datetime, --查询的结束时间
@msdm varchar(1)=null --查询的门市代码
as
select top 12 id=identity(int,1,1) into #t from syscolumns
if isnull(@msdm,'')=''
select a.msdm
,sj=case a.fs when '月付' then dateadd(month,b.id-month(a.JDATE),a.JDATE)
when '季付' then dateadd(month,(b.id-1)*3,a.jdate)
else a.JDATE end
,a.je
from TAB_JNGZ a,#t b
where b.id between
case a.fs when '月付' then month(FDATE)
when '季付' then (month(FDATE)-1)/3+1
else 1 end and
case a.fs when '月付' then month(TDATE)
when '季付' then (month(TDATE)-1)/3+1
else 1 end
and FDATE>=@fdate and TDATE<=@tdate
order by a.msdm
else
select a.msdm
,sj=case a.fs when '月付' then dateadd(month,b.id-month(a.JDATE),a.JDATE)
when '季付' then dateadd(month,(b.id-1)*3,a.jdate)
else a.JDATE end
,a.je
from TAB_JNGZ a,#t b
where b.id between
case a.fs when '月付' then month(FDATE)
when '季付' then (month(FDATE)-1)/3+1
else 1 end and
case a.fs when '月付' then month(TDATE)
when '季付' then (month(TDATE)-1)/3+1
else 1 end
and FDATE>=@fdate and TDATE<=@tdate and msdm=@msdm
order by a.msdm
go
create table TAB_MSDM(id int identity(1,1),msdm varchar(1),ssdq varchar(20))
insert into TAB_MSDM
select 'A','北京'
union all select 'B','上海'
union all select 'C','天津'
union all select 'D','河北'--一个用来存储门市的缴纳规则表
create table TAB_JNGZ(ID int identity(1,1),MSDM varchar(1)
,FDATE datetime,TDATE datetime,JDATE datetime,JE decimal(20,0),FS varchar(10))
insert into TAB_JNGZ
select 'A','2003-1-1','2003-3-31','2003-1-10',20000,'月付'
union all select 'B','2003-2-1','2003-4-30','2003-2-10',15000,'月付'
union all select 'C','2003-1-5','2003-7-4','2003-1-10',50000,'季付'
union all select 'A','2003-4-1','2003-6-30','2003-4-5',55000,'季付'go
create proc p_qry
@fdate datetime, --查询的开始时间
@tdate datetime, --查询的结束时间
@msdm varchar(1)=null --查询的门市代码
as
select top 12 id=identity(int,1,1) into #t from syscolumns
if isnull(@msdm,'')=''
select a.msdm
,sj=case a.fs when '月付' then dateadd(month,b.id-month(a.JDATE),a.JDATE)
when '季付' then dateadd(month,(b.id-1)*3,a.jdate)
else a.JDATE end
,a.je
from TAB_JNGZ a,#t b
where b.id between
case a.fs when '月付' then month(FDATE)
when '季付' then (month(FDATE)-1)/3+1
else 1 end and
case a.fs when '月付' then month(TDATE)
when '季付' then (month(TDATE)-1)/3+1
else 1 end
and FDATE>=@fdate and TDATE<=@tdate
order by a.msdm
else
select a.msdm
,sj=case a.fs when '月付' then dateadd(month,b.id-month(a.JDATE),a.JDATE)
when '季付' then dateadd(month,(b.id-1)*3,a.jdate)
else a.JDATE end
,a.je
from TAB_JNGZ a,#t b
where b.id between
case a.fs when '月付' then month(FDATE)
when '季付' then (month(FDATE)-1)/3+1
else 1 end and
case a.fs when '月付' then month(TDATE)
when '季付' then (month(TDATE)-1)/3+1
else 1 end
and FDATE>=@fdate and TDATE<=@tdate and msdm=@msdm
order by a.msdm
goexec p_qry '2003-1-1','2003-8-31','A'
exec p_qry '2003-1-1','2003-8-31','C'--删除数据测试环境
drop table TAB_MSDM,TAB_JNGZ
drop proc p_qry
/*--结果:
msdm sj je
---- ------------------------------------------------------ ---------------
A 2003-01-10 00:00:00.000 20000
A 2003-02-10 00:00:00.000 20000
A 2003-03-10 00:00:00.000 20000
A 2003-07-05 00:00:00.000 55000(所影响的行数为 4 行)
(所影响的行数为 12 行)msdm sj je
---- ------------------------------------------------------ ----------
C 2003-01-10 00:00:00.000 50000
C 2003-04-10 00:00:00.000 50000
C 2003-07-10 00:00:00.000 50000(所影响的行数为 3 行)
--*/
http://expert.csdn.net/Expert/topic/2480/2480343.xml?temp=.7713434
谢谢!