Try something like this for the first step... not sure why it was put into a loop... unless I'm missing something...
declare @LoanID as table (LoanID varchar(10))
insert into @LoanID(LoanID)
select LoanID from service.dbo.loan where nextcoupondate <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and nextcoupondate > '4/10/2009' and LoanID not in
(select LoanID from M1CustLetters where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from service.dbo.usr_m1_letter_requests
where customerstatement >= convert(varchar(10),dateadd(dd,-4,getdate()),101)
and customerstatement <= convert(varchar(10),dateadd(dd,3,getdate()),101)
and loanid not in (select loanid from m1custletters
where StatementDate >= convert(varchar(10),dateadd(dd,-4,getdate()),101)))
and LoanID not in (select LoanID from service.dbo.Status where CouponStop = 1)
or
loanid in (select loanid from service.dbo.loan where right(loanid,7) not in
(select loanid from m1lettersqueue)
and lastbilldate <= getdate() and lastbilldate >= dateadd(dd,-14,getdate())
and loanid not in (select loanid from service.dbo.status where CouponStop = 1)
and loanid not in (select loanid from m1custletters where statementdate >= dateadd(dd,-30,getdate()))
and duedate <= dateadd(dd,30,getdate()))
update l
set l.lastbilldate = dateadd(mm,-1,l.nextcoupondate)
from service.dbo.loan l
where exists (select 1 from @LoanID i where l.loanid = i.LoanID)
and lastbilldate is null
insert into m1lettersqueue (loanid,statementdate)
select right(l.loanid,7),convert(varchar(10),getdate(),101)
from @LoanID l
where not exists(select 1 from m1lettersqueue m where m.loanid = right(l.LoanID,7)
and m.statementdate = convert(varchar(10),getdate(),101)))
I would normally view the statements as a select to see the data first before I actually do an update or insert...
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D