How to automate the query until the condition OK? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to automate the query until the condition OK?

Author

Topic

rasta
Starting Member

23 Posts

Posted - 2012-01-09 : 12:01:18

I have the following table and the query by which
I insert number of days until equal to 91
WHERE [DniPoSPl] IS NULL
OR correct the number of days if there s a mistake such as p1.DniPoSPl is not equal to p2.DniPoSPl - DATEDIFF(Day,p1.bus_dt, p2.bus_dt).
It works fine, however, I have to run the query multiply times until 0 observations is changed.
How could I automate the query to run it automatically?
Thanks!

Create table tmp_portfolio (ID int not null identity, ClientID int not null, bus_dt datetime not null, DniPoSPl int null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (1, '2011-03-30',null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (1, '2011-04-30',null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (1, '2011-05-31' , 640)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (1, '2011-06-30' ,821)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-06-30' , null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-07-31' ,null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-08-30' ,null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-09-30', null)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-10-30' , 943)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-11-30' , 1127)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-12-31' , 1308)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2011-01-31' , 1492)
Insert into tmp_portfolio (ClientID, bus_dt, DniPoSPl)
Values (2, '2010-02-28' ,1673)

select * from tmp_portfolio

update p1 set p1.DniPoSPl = p2.DniPoSPl - DATEDIFF(Day,p1.bus_dt, p2.bus_dt)
from tmp_portfolio p1
inner join
tmp_portfolio p2
on p2.ClientID=p1.ClientID and p2.ID<>p1.ID
where
(p1.DniPoSPl IS NULL OR (p1.DniPoSPl < (p2.DniPoSPl - (DATEDIFF(Day,p1.bus_dt, p2.bus_dt)+10)))) AND
DATEDIFF(Day,p1.bus_dt, p2.bus_dt) between 1 and 35 and p2.DniPoSPl >=(91+DATEDIFF(Day,p1.bus_dt, p2.bus_dt))

select * from tmp_portfolio

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-01-09 : 12:16:24

maybe something like
select 1
while @@rowcount <> 0
update ....

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources