22,209
社区成员
发帖
与我相关
我的任务
分享
--把 if while删掉,把条件改成
WHERE a.PeriodRepyDate>= dateadd(day,0,@VacationDate)
AND a.PeriodREpyDate<dateadd(day,@dateDiff,@VacationDate)
and isnull(a.RepayAmount-a.receivableAmount,0)>0
if object_id('t') is not null
drop table t;
create table t (id int identity(1,1),edate date)
go
insert into t
select '20131201'
union all select '20131202'
union all select '20131203'
union all select '20131204'
union all select '20131205'
union all select '20131206'
union all select '20131207'
union all select '20131208'
union all select '20131209'
union all select '20131210'
go
CREATE PROCEDURE [dbo].[proc_AdvReportMutSelect]
@VacationDate datetime,
@dateDiff int
AS
declare @i int
set @i=0
WHILE (@i<@dateDiff)
BEGIN
select *
from t
where convert(varchar(10),t.edate,120)=convert(varchar(10),dateadd(day,@i,@VacationDate),120)
SET @i=@i+1
END
go
Exec [proc_AdvReportMutSelect] '20131201',3
if object_id('t') is not null
drop table t;
create table t (id int identity(1,1),edate date)
go
insert into t
select '20131201'
union all select '20131202'
union all select '20131203'
union all select '20131204'
union all select '20131205'
union all select '20131206'
union all select '20131207'
union all select '20131208'
union all select '20131209'
union all select '20131210'
go
go
CREATE PROCEDURE [dbo].[proc_AdvReportMutSelect]
@VacationDate datetime,
@dateDiff int
AS
declare @t table(id int,edate date)
declare @i int
set @i=0
WHILE (@i<@dateDiff)
BEGIN
insert into @t
select *
from t
where convert(varchar(10),t.edate,120)=convert(varchar(10),dateadd(day,@i,@VacationDate),120)
SET @i=@i+1
END
select *
from @t;
go
Exec [proc_AdvReportMutSelect] '20131201',3
ALTER PROCEDURE [dbo].[proc_AdvReportMutSelect]
@VacationDate datetime,
@dateDiff int
AS
declare @i int
set @i=0
WHILE (@i<@dateDiff)
BEGIN
select
b.CIFNm,b.CustomNm,dd.phone,(SUM(isnull(a.RepayAmount-a.receivableAmount,0))) Repaymoney,
kk.PRepayDate,
b.LoanNm,b.LoanStratDate,b.LoanAmount
from
(
select * from InterestReceive
union all select * from InterestReceivetemp
) a
left join
(
select max(convert(varchar(10),PeriodRepyDate,120)) PRepayDate,ContractNm repd from
(
select * from InterestReceive
union all select * from InterestReceivetemp) aa
group by ContractNm
)
kk
on kk.repd=a.ContractNm
left join OPSInf b on a.ContractNm=b.LoanNm
left join (
select CIFNm,phone from
(
select CIFNm,case LoanType when '个人客户' then
case
when SUBSTRING(PerMobliePhB,0,PATINDEX('%|%',PerMobliePhB))='' then PerMobliePhB
else SUBSTRING(PerMobliePhB,0,PATINDEX('%|%',PerMobliePhB))
end
when '企业客户' then CompanyContactMphontA else '' end phone,SalesDate
from LoanInf where Followstate='已审核'
) c
left join
(select MAX(salesdate) salsd ,CIFNm cfin from LoanInf where
Followstate='已审核' group by CIFNm) b
on
b.cfin=c.CIFNm
where salsd = c.SalesDate
) dd
on dd.CIFNm=a.CIFNm
where convert(varchar(10),a.PeriodRepyDate,120)=convert(varchar(10),dateadd(day,@i,@VacationDate),120)
and isnull(a.RepayAmount-a.receivableAmount,0)>0
group by
b.CIFNm,b.CustomNm, dd.phone,kk.PRepayDate,b.LoanNm,b.LoanStratDate,b.LoanAmount
SET @i=@i+1
--修改了这里,每次都把@VacationDate 增加1天
set @VacationDate= DATEADD(day,1,@VacationDate)
END