February 26, 2004 at 8:25 pm
I need to send a data set (non-contacts from a dialer) to a legacy system, but am making almost no progress with the extract.
I need to start with records from the Call table:
CREATE TABLE [dbo].[Call] (
[CallID] [int] IDENTITY (1000, 1) NOT NULL ,
[ProspectID] [int] NULL ,
[TerminationCode] [varchar] (10)
[Uploaded] [char] (1)
) ON [PRIMARY]
where the callid is the max for a particular prospectid if the terminationcode is >'a' and <'z' (in the rptgroupitem table as part of group 91).
CREATE TABLE [dbo].[RptGroupItem] (
[RptGroupID] [int] NOT NULL ,
[TerminationCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
GO
This query returns the candidate ProspectIDs to be sent to the legacy system.
(SELECT prospectid
FROM call
WHERE callid IN
(SELECT MAX(callid)
FROM call
GROUP BY prospectid)
AND terminationcode IN
(SELECT terminationcode
FROM rptgroupitem
WHERE rptgroupid = 91)
AND uploaded = 'N'
The wrinkle: Using these ProspectIDs I need to get all of the ProspectIds from a Prospect table which have a matching Customer Account Number (CustCredAct)
CREATE TABLE [dbo].[Prospect] (
[ProspectID] [int] IDENTITY (147186, 1) NOT NULL ,
[CustCredAct] [varchar] (11)
then go back to the Call table and determine: for a given ProspectID, if there is an entry for another ProspectId with the same CustCredAct, and that entry has a TerminationCode not in 'a - z', then don't send the given ProspectId to the legacy system.
Examples
| CallId | ProspectId | TC | Account | |||
| 1 | 1 | a | 1 | |||
| 2 | 1 | a | 1 | |||
| 3 | 1 | a | 1 | |||
| 4 | 1 | a | 1 | Max for this ID, candidate | ||
| 5 | 1 | PTP | 1 | Contact, don't send 4 | ||
| 6 | 2 | b | 2 | |||
| 7 | 2 | b | 2 | Candidate | ||
| 8 | 3 | b | 2 | Non-contact, send 7 & 8 |
February 26, 2004 at 9:56 pm
select a.ProspectID
from call a
inner join Prospect b on a.ProspectID = b.ProspectID
inner join call c on b.CustCredAct = c.CustCredAct
where a.callid = (select max(d.callid) from call d where a.ProspectID = d.ProspectID)
and exists(select * from RptGroupItem e where e.RptGroupID = 91 and a.TerminationCode = e.TerminationCode)
and a.Uploaded = 'N'
and not exists(select * from RptGroupItem f where f.RptGroupID = 91 and c.TerminationCode = f.TerminationCode)
February 26, 2004 at 10:33 pm
create function fn_pickup (@ProspectID int, @CustCredAct varchar(11))
returns bit
as
begin
declare @return bit
set @return = 1
if exists(
select * from call a
inner join Prospect b on a.ProspectID = b.ProspectID
where b.CustCredAct = @CustCredAct and a.ProspectID != @ProspectID
and not exists(select * from RptGroupItem c where c.RptGroupID = 91 and a.TerminationCode = c.TerminationCode))
set @return = 0
return @return
end
go
select a.ProspectID
from call a
inner join Prospect b on a.ProspectID = b.ProspectID
where a.callid = (select max(d.callid) from call d where a.ProspectID = d.ProspectID)
and exists(select * from RptGroupItem e where e.RptGroupID = 91 and a.TerminationCode = e.TerminationCode)
and a.Uploaded = 'N'
and dbo.fn_pickup(a.ProspectID, b.CustCredAct) = 1
February 27, 2004 at 8:12 am
I probably didn't explain this well enough
In the first result set (using the query of the 2nd solution provided by wz), the TerminationCode of CallId 119 is 'a' - so ProspectID 3859 and 3860 should be uploaded to the legacy app.
However, if the TerminationCode of CallId 119 is not in the 'a' - 'z' grouping, ProspectID 3859 and 3860 should be removed from the result set - but it isn't (2nd result set - TerminationCode is set to 'PTP' for CallId 119).
The basis here is; if a contact is made on any contract for a given account, all open contracts are to be discussed. So it may be possible all of the auto attempts by the dialer result in non-contact for one contract, but results in contact for another contract for the account -- which equates to a contact for all contracts (assuming the agent does the job and discusses all contracts while working the contact).
| Callid | ProspectID | Temination | Account |
| 112 | 3859 | a | 29253 |
| 119 | 3860 | a | 29253 |
| 116 | 3861 | a | 35402 |
| 120 | 3862 | a | 35402 |
| 118 | 3870 | a | 76181 |
| 121 | 3871 | a | 76181 |
| |||
| 112 | 3859 | a | 29253 |
| 116 | 3861 | a | 35402 |
| 120 | 3862 | a | 35402 |
| 118 | 3870 | a | 76181 |
| 121 | 3871 | a | 76181 |
February 27, 2004 at 8:31 am
Job stress is making me forget my manners.
wz700 - I do appreciate your investment of time in reviewing the question.
Your 2nd solution does return the same result as the original query
SELECT prospectid, terminationcode
FROM call
WHERE callid IN
(SELECT max(callid)
FROM call
GROUP BY prospectid)
AND terminationcode IN
(SELECT terminationcode
FROM rptgroupitem
WHERE rptgroupid = 91)
AND uploaded = 'N'
order by prospectid
3859 a
3861 a
3862 a
3870 a
3871 a
It is the from Call to Prospect for CCAN back to Call for siblings with non-contacts only that has been stumping me.
On solution one, life would indeed be much easier if CCAN were in the Call table. The system changes to add this one field are extensive but may be the only option to support the request without major impact of the joins.
Thanks.
February 29, 2004 at 4:41 pm
Here is my test case.
select * from call
CallID ProspectID TerminationCode Uploaded
----------- ----------- --------------- --------
112 3859 a N
119 3860 a N
116 3861 a N
120 3862 a N
118 3870 a N
121 3871 a N
select * from Prospect
ProspectID CustCredAct
----------- -----------
3859 29253
3860 29253
3861 35402
3862 25402
3870 76181
3871 76181
select * from RptGroupItem
RptGroupID TerminationCode
----------- ---------------
91 a
90 PTP
select a.ProspectID
from call a
inner join Prospect b on a.ProspectID = b.ProspectID
where a.callid = (select max(d.callid) from call d where a.ProspectID = d.ProspectID)
and exists(select * from RptGroupItem e where e.RptGroupID = 91 and a.TerminationCode = e.TerminationCode)
and a.Uploaded = 'N'
and dbo.fn_pickup(a.ProspectID, b.CustCredAct) = 1
ProspectID
-----------
3859
3861
3870
3860
3862
3871
Change callid terminationcode
update call set terminationcode = 'PTP' where callid = 119
select a.ProspectID
from call a
inner join Prospect b on a.ProspectID = b.ProspectID
where a.callid = (select max(d.callid) from call d where a.ProspectID = d.ProspectID)
and exists(select * from RptGroupItem e where e.RptGroupID = 91 and a.TerminationCode = e.TerminationCode)
and a.Uploaded = 'N'
and dbo.fn_pickup(a.ProspectID, b.CustCredAct) = 1
ProspectID
-----------
3861
3870
3862
3871
And you can see, the 3859 and 3861 are not there.
February 29, 2004 at 4:46 pm
It should be 3859 and 3860 are not there.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply