December 17, 2009 at 3:56 pm
Working on a stored procedure to move some files along. Files are supposed to populate in specific queues when certain criteria are met.
But I can't seem to figure this one. Anyone help?
--------------
AS
BEGIN
DECLARE @in_xtelelink int
declare @currstatus varchar(25)
declare @appdate varchar(12)
declare @sort varchar(50)
declare @hphone varchar(14)
declare @xcheckout bit
declare @queint
declare @in_xqueuefrom int
declare @in_xqueuetoint
declare @currstatusdate varchar (50)
declare @fulfillment bit
declare @xlastcallvarchar(20)
declare @xoperator varchar(20)
declare @client_id int
declare @client_name varchar(50)
declare @companyshort varchar(50)
declare @xcallbackdatetime
declare @statusflagbit
DECLARE cursor1 CURSOR READ_ONLY FOR
SELECTp.xtelelink, ts.status, c.dateloaded, ts.last_updated_date, t.xqnumber, c.fulfillment,
t.xlastcall, t.xoperator, p.client_id, p.primaryname, t.xcallback, p.counter1
FROMdev.dbo.casemanagement_list p
JOINdev.dbo.telescrp t
on t.xtelelink = p.xtelelink
JOIN dev.dbo.stage_dlr_casemanagement c
ON c.filenumber = p.filenumber
JOINbeatles.client_manager_production.dbo.T_status ts
on ts.filenum = c.filenumber
where ts.status in ('App Hold', 'App Hold 2', 'App Complete', 'Cancelled', 'App Reopened',
'Exam Complete', 'Exam Complete No App', 'Underwriting', 'Underwriting - APS',
'Underwriting-Tentative offer', 'Other Than Applied','Approved as Applied',
'Better Than Applied For', 'Pending Issue', 'Pending Reissue',
'Underwriting-Tentative offer', 'Other Than Applied', 'Delivery Requirements',
'In Force')
OPEN cursor1
FETCH NEXT FROM cursor1 INTO
@in_xtelelink, @currstatus, @appdate, @currstatusdate, @in_xqueuefrom, @fulfillment,
@xlastcall, @xoperator, @client_id, @client_name, @xcallback,@statusflag
WHILE @@FETCH_STATUS = 0
BEGIN
set @in_xqueueto = @in_xqueuefrom
set @in_xqueueto = 0
If (datediff(dd,@xlastcall,getdate()) < 2
and @xlastcall is not null)
Set @in_xqueueto = 2
set @xcallback = getdate ()
If (@currstatus IN ('App Hold', 'App Hold 2', 'App Reopened', 'App complete')
AND datediff(dd,@xlastcall, getdate() ) = 2)
OR (@currstatus IN ('Cancelled')
AND (datediff(dd,@currstatusdate, getdate() ) = 7))
set @in_xqueueto = 3
set @xcallback = getdate()
IF (@currstatus in ('Exam Complete')
AND (@companyshort in ('Liberty Life', 'Transamerica Occidental', 'Ohio National')
and datediff(dd,@currstatusdate, getdate() ) = 10)
OR (@companyshort in ('Pruco')
and datediff(dd,@currstatusdate, getdate() ) = 7)
Or (@statusflag = 1 and datediff(dd,@currstatusdate,getdate()) = 4))
set @in_xqueueto = 3
set @xcallback = getdate()
set @statusflag = 1
IF (@currstatus in ('Exam Complete No App')
AND @fulfillment = 0)
AND (datediff(dd,@currstatusdate,getdate() ) = 1)
OR (datediff(dd,@currstatusdate, getdate() ) = 4)
OR (datediff(dd,@currstatusdate,getdate() ) = 11)
OR (datediff(dd,@currstatusdate,getdate() ) =22)
set @in_xqueueto = 3
set @xcallback = getdate()
set @statusflag = 1
IF (@currstatus in ('Exam Complete No App')
AND @fulfillment = 1)
and (datediff(dd,@currstatusdate,getdate()) IN ('3','6','9','12', '15','18','21'))
set @in_xqueueto = 4
IF (@currstatus in ('Underwriting'))
set @in_xqueueto = 5
IF (@currstatus in ('Underwriting-APS'))
set @in_xqueueto = 6
IF (@currstatus in ('Underwriting-Tentative offer', 'Other Than Applied'))
set @in_xqueueto = 7
IF (@currstatus in ('Approved as Applied', 'Better Than Applied For', 'Pending Issue', 'Pending Reissue'))
set @in_xqueueto = 8
if (@currstatus in ('Delivery Requirements'))
set @in_xqueueto = 9
if (@currstatus in ('In Force'))
set @in_xqueueto= 10
if (@currstatus in ('Declined', 'Postponed'))
exec casemanagement_queue_update @in_xtelelink, @in_xqueuefrom, @in_xqueueto
fetch next from cursor1 into @in_xtelelink, @currstatus,@appdate, @currstatusdate, @in_xqueuefrom, @fulfillment,
@xlastcall, @xoperator, @client_id, @client_name, @xcallback, @statusflag
END
close cursor1
deallocate cursor1
END
-----------
pretty plz? 🙂
December 17, 2009 at 4:03 pm
SQLRookie 6502 (12/17/2009)
Files are supposed to populate in specific queues when certain criteria are met.
What is the problem you are running into (programmatic or logic)? What are the specific queues and certain requirements?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 17, 2009 at 4:45 pm
the queues are for a dialer application, and the requirements are that specific customers are loaded based on their current status in the system.
The procedure would be run once a day, around 4am, in order to populate the queues.
A customer can only exist in one queue at a time, hence that second procedure that's referenced in the script, which kills it from the queues its coming from and inserts it into the destination queue, by its unique identifier (telelink).
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply