MSSql Scripts for creating Calling List

[jason]

Hi Guys,

Can some one help me out with an Ms Sql query that Iam trying to develop for building the calling list?

I have a temperory table that has the custom fields and also the contact info(mobile,business,home) in a single  row.And based on if any one of the contact info is null or not I would need to create 2-3 rows in the final calling list table along with the chain id,chain_n and the other mandatory fields.

I did try creating some sql scripts based on my exp on pl\sql but it doesnt seems to be working well with mssql.So if some one has some sample scripts, which I can use as a reference, it would be helpful and save me of a lot of time.

Thanks

[cavagnaro]

The calling lists should be created using OCM, but previously defining the format (structure) of the table using CME. You can't create tables directly on the DB.

[ecki]

Hi,

Cav, Yes you can create calling list tables, you just have to make sure that your table has enough fields, correct type of fields, primary key and indexes. The last are optional as OCM will recognize missing indexes and will offer you to create them for you.

Jason, what exactly does not work? Can you describe the errors you are getting?

[cavagnaro]

?? Yes you can but remember that also Store Procedures are created. Are those created after by OCM?

[ecki]

Sorry Cav, what for you need stored procedure for calling list? When you create new calling list through OCM, not a single stored procedure is created.

[cavagnaro]

Are you sure? I work with MSSQL and everytime a SP is created like this:

[quote]
create procedure sm110112127120
(@ChainID varchar(20), @ChainN varchar(20), @InsRecordId varchar(20) OUTPUT, @MaxChainId varchar(20) OUTPUT, @MaxChainN varchar(20) OUTPUT)
AS
BEGIN
declare @recid int
declare @chid int
declare @chn int
select @MaxChainId = '-1'
select @MaxChainN = '-1'
select @InsRecordId = '0'
select @chid = convert (numeric, @ChainID)
select @chn  = convert (numeric, @ChainN)
insert into maxlistid select 'ib3' as tablename,max(record_id),max(chain_id) from ib3
IF @chid > 0 AND @chn > 0
BEGIN
select @recid = (select count(*) from ib3 where chain_id = @chid AND chain_n = @chn)
IF @recid > 0
RETURN
BEGIN TRANSACTION
select @recid = max(maxrecid) from maxlistid  where tablename='ib3'
select @recid   = isnull(@recid, 0)+1
insert into maxlistid (tablename,maxrecid,maxchainid) values('ib3',@recid,@chid)
COMMIT TRANSACTION
delete from maxlistid where maxrecid < @recid AND maxchainid < @chid AND tablename='ib3'
select @InsRecordId = convert (varchar(20), @recid)
select @MaxChainId  = convert (varchar(20), @chid)
select @MaxChainN  = convert (varchar(20), @chn)
RETURN
END
IF @chid > 0
BEGIN
select @recid = (select count(*) from ib3 where chain_id = @chid)
IF @recid > 0
BEGIN
select @chn = max(chain_n) from ib3 where chain_id = @chid
select @chn   = isnull(@chn, 0)+1
IF @chn < 0
select @chn   = 0
END
ELSE
select @chn  = 0
BEGIN TRANSACTION
select @recid = max(maxrecid) from maxlistid  where tablename='ib3'
select @recid   = isnull(@recid, 0)+1
insert into maxlistid (tablename,maxrecid,maxchainid) values('ib3',@recid,@chid)
COMMIT TRANSACTION
delete from maxlistid where maxrecid < @recid AND maxchainid < @chid AND tablename='ib3'
select @InsRecordId = convert (varchar(20), @recid)
select @MaxChainId  = convert (varchar(20), @chid)
select @MaxChainN  = convert (varchar(20), @chn)
RETURN
END
BEGIN TRANSACTION
select @chid = max(maxchainid), @recid = max(maxrecid) from maxlistid  where tablename='ib3'
select @chid = isnull (@chid, 0)+1
select @recid   = isnull (@recid, 0)+1
insert into maxlistid (tablename,maxrecid,maxchainid) values('ib3',@recid,@chid)
COMMIT TRANSACTION
delete from maxlistid where maxrecid < @recid AND maxchainid < @chid AND tablename='ib3'
select @InsRecordId = convert (varchar(20), @recid)
select @MaxChainId  = convert (varchar(20), @chid)
select @MaxChainN  = '0'
END

GO
[/quote]

Where ib3 is the table name for my calling list. Even if you check OCS logs you will see that it executes the SP frequently.

[ecki]

Yes, this is but connected to campaigns and not to calling lists directly. When you load your campaign which is using particular calling list for first time, OCS creates these stored procedures to manage contacts in calling lists.

[jason]

Iam facing an issue with getting the right logic to loop through the temp table and check for each record ...if an record has all the three contact info then insert this record into the final calling list table as 3 records along with the other mandantory fields like chainid,chain_n etc.And if a record doesnt have all the 3 contact info then check for null values and then insert this record into the final calling list accordingly.

[ecki]

But what is failing? Do not understand. Does have OCS/OCM issue with this?

[cavagnaro]

[quote author=ecki link=topic=4153.msg18460#msg18460 date=1241495850]
Yes, this is but connected to campaigns and not to calling lists directly. When you load your campaign which is using particular calling list for first time, OCS creates these stored procedures to manage contacts in calling lists.
[/quote]
Oh ok, thanks for the clarification

[ecki]

You are very welcome.

[jason]

Hi Cav,

Sorry I couldn't reply to your comment earlier ...i was caught in some other issues and this went into the back burner

Basically, Iam trying to get the right sql script logic to do the data transformation .The staging table from where I need to tranform to the final calling list table has around 50 cols...the staging table has 3 cols which has the customer ph no.So I will to check out for null values and based on it I would have to insert it into the final calling list table ...and along with this I would also need to assign the chain_n,record id and the other mand fields.

The only way I can think of is by using case statements ...but with case statements and 50 cols the sql query would be huge and Iam concerned that it would have performance impact aswell .So I wanted to check if there is any better way of doing it

ta