Loop this code?? - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Loop this code??

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2007-06-05 : 09:53:50

I have a query as shown below. Currently, @ThisDate is set to 1 so that when the query executes the Date parameter is set to GETDATE()-1. If @ThisDate is set to 2 then the Date parameter would be GETDATE()-2 and so on.

I need to let the user pass a Loop value parameter and then somehow Loop the code from 1 to the users specified amount, whilst changing the @ThisDate value to reflect the current Loop iteration.

So, lets say the user passes 3 into a LoopCount parameter. The code would loop 3 times with the first loop setting @ThisDate to 1, the 2nd setting it to 2 and the 3rd setting @ThisDate to 3 then finishing. Is this possible?

DECLARE @ThisDate Int
TRUNCATE TABLE LBMM_AgentCounter --Only at the beginning

SELECT @ThisDate = 1

INSERT  LBMM_AgentCounter
(AgentId, DateofCall, Agent, NoofCalls, SM)

SELECT distinct A.agentid, A.[dateofcall], A.agentname as 'Agent', A.noofcallsmade as 'NoofCalls', ISNULL(SM.[Name], 'UNKNOWN') as 'SM'
FROM (select distinct  dateofcall, ma.agentid, agentname, count(*) as noofcallsmade
	from callrslt_archive ca inner join magent ma  
	on ca.agentid=ma.agentid 
where dateofcall = convert(int, convert(varchar, getdate()-@ThisDate, 112))
and (ma.agentid  in 
(
SELECT   distinct  dbo.LBMM_Agents.diallerid
	FROM         dbo.LBMM_Agents INNER JOIN
                      dbo.LBMM_TimesheetFact ON dbo.LBMM_Agents.keyid = dbo.LBMM_TimesheetFact.AgentID
where [date]=convert(int, convert(varchar, getdate()-@ThisDate, 112)) AND (ProductionHours = 0 AND OvertimeHours = 0)  
)
or ma.agentid not in
(
SELECT   distinct  dbo.LBMM_Agents.diallerid
	FROM         dbo.LBMM_Agents INNER JOIN
                      dbo.LBMM_TimesheetFact ON dbo.LBMM_Agents.keyid = dbo.LBMM_TimesheetFact.AgentID
where [date]=convert(int, convert(varchar, getdate()-@ThisDate, 112))
)
)		
group by ma.agentid, agentname,dateofcall
having count(*)>1) A
left Join LBMM_Agents on A.agentid = LBMM_Agents.diallerid and LBMM_Agents.dateid = ( select max(dateid)from LBMM_Agents)
left join SM SM on LBMM_Agents.parentkeyid = SM.KeyID WHERE ISNULL(RIGHT(SM.[Name],12), 'UNKNOWN') <>'[Historical]'
order by agentname

nr
SQLTeam MVY

12543 Posts

Posted - 2007-06-05 : 10:33:35

create proc a
@loop int
as
DECLARE @ThisDate Int
TRUNCATE TABLE LBMM_AgentCounter --Only at the beginning

SELECT @ThisDate = 0

while @loop > 0
begin
select @loop = @loop - 1, @ThisDate = @ThisDate +1

-- all the rest of the code

end

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

patshaw
Posting Yak Master

177 Posts

Posted - 2007-06-05 : 17:23:45

Thanks Nigel.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources