declare @t1 table(id int,listdate datetime,amount int)
insert into @t1 select 1,'2005-02-01',20
insert into @t1 select 2,'2005-02-05',30
insert into @t1 select 3,'2005-02-07',40
insert into @t1 select 4,'2005-02-08',50
insert into @t1 select 5,'2005-02-12',60
insert into @t1 select 6,'2005-02-14',70declare @A int,@I int,@D datetime
set @A=200
set @I=6set rowcount 1000
select identity(int,1,1) as id into #T from sysobjects
set rowcount 0select
top 1 @D=c.listdate
from
(select
dateadd(dd,(b.id*@I-1),a.listdate) as listdate
from
(select min(listdate) as listdate from @t1) a,#t b) c,
@t1 d
where
c.listdate>=d.listdate
and
c.listdate<=getdate()
group by c.listdate
having sum(d.amount)>@A
order by c.listdateselect case when @D is null then '不提示' else '欠款,欠款日期='+convert(char(10),@D,120) end--
/*
欠款,欠款日期=2005-02-18
*/drop table #T
insert into @t1 select 1,'2005-02-01',20
insert into @t1 select 2,'2005-02-05',30
insert into @t1 select 3,'2005-02-07',40
insert into @t1 select 4,'2005-02-08',50
insert into @t1 select 5,'2005-02-12',60
insert into @t1 select 6,'2005-02-14',70declare @A int,@I int,@D datetime
set @A=200
set @I=6set rowcount 1000
select identity(int,1,1) as id into #T from sysobjects
set rowcount 0select
top 1 @D=c.listdate
from
(select
dateadd(dd,(b.id*@I-1),a.listdate) as listdate
from
(select min(listdate) as listdate from @t1) a,#t b) c,
@t1 d
where
c.listdate>=d.listdate
and
c.listdate<=getdate()
group by c.listdate
having sum(d.amount)>@A
order by c.listdateselect case when @D is null then '不提示' else '欠款,欠款日期='+convert(char(10),@D,120) end--
/*
欠款,欠款日期=2005-02-18
*/drop table #T
解决方案 »
- 关于SQL查询判断语句
- 关于TestDirector的控件下载问题,出现cgi错误
- 【求助】请教如何连续存入信息到同一个字段,信息之间以逗号隔开
- 关于存储过程的问题,急
- sql语句。关于group by 和where之间取个中立点。
- 请问怎样用SQL语句修改表的关键字?
- 用DBCC TRACEON(1204,3605,-1)跟踪死锁,并没有在日志中发现死锁记录
- 语法问题
- 请问如何用SQL语句判断某个数据库是否存在?期盼大虾指导!
- 存储过程中,@@IDENTITY为何老是返回null值??????????????
- 服务器作好了订阅与发布后,日志增长很快,如何控制它的增长?
- 今天见鬼了,新装xp后,一个一定正确的程序连不上数据库,可是在odbc中配置一下就行了,然后我把dsn删了也还可以.??
insert t_a
select '2006-01-01' ,20 union all
select '2006-01-05' ,30 union all
select '2006-01-07' ,40 union all
select '2006-01-08' ,50 union all
select '2006-01-12' ,60 union all
select '2006-01-14' ,70
select * from t_aif exists(select 1 from sysobjects where id=object_id(N'f_getPrompt') and xtype=N'FN')
drop function f_getPromt
go
create function f_getPrompt(@stDate datetime,@limit int,@day int,@enDate datetime)
returns @re table(prompt varchar(30))
as
begin
set nocount on
declare @result varchar(10)
declare @t table(newlistdate datetime,listdate datetime,amount int) if @day>0
begin
insert into @t
select t.newlistdate,listdate=max(listdate),amount=sum(t.amount)
from (select id,
listdate,
newlistdate=dateadd(dd, - datediff(dd,@stDate, listdate) % @day, listdate),
amount
from t_a
where listdate between @stdate and @enDate
) t
group by t.newlistdate
order by t.newlistdate,t.listdate
select top 1 @result=convert(varchar(10),listdate ,120)
from @t t
where (select sum(amount) from @t where listdate<=t.listdate)>=@limit
if @result is not null
insert @re select '欠款,欠款日期='+@result
end
set nocount off
return
end
go
--调用
declare @endate datetime
set @endate=getdate()
select * from dbo.f_getPrompt('2006-01-01',100,2,@endate)--结果
/*
prompt
------------------------------
欠款,欠款日期=2006-01-08
*/select * from dbo.f_getPrompt('2006-01-01',200,6,'2006-01-10')
--结果
/*
*/
--删除测试数据
drop table t_a