Niths_1299
Guppy
ALTER PROCEDURE [dbo].[LCM_CAMPAIGNINFO]
(
@CAMPAIGNID VARCHAR(64),
@LISTCOUNT INTEGER OUTPUT,
@OPENCOUNT INTEGER OUTPUT,
@PCBCOUNT INTEGER OUTPUT,
@LASTSTART DATETIME OUTPUT,
@CONTACTSCOUNT INTEGER OUTPUT,
@CLOSEDCOUNT INTEGER OUTPUT,
@LASTEND DATETIME OUTPUT,
@NEVERDIALED INTEGER OUTPUT,
@DIALOUTS INTEGER OUTPUT,
@SUCCESSCOUNT INTEGER OUTPUT,
@CBCOUNT INTEGER OUTPUT,
@SUCCESSBO INTEGER OUTPUT,
@FAILEDBO INTEGER OUTPUT,
@VOICEOUTCOMECOUNT INTEGER OUTPUT,
@SUCCESSVOICEOUTCOMECOUNT INTEGER OUTPUT,
@FAILEDVOICEOUTCOMECOUNT INTEGER OUTPUT
)
AS
BEGIN
SELECT @LISTCOUNT = COUNT(DISTINCT(LISTID)) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @OPENCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 0
SELECT @PCBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NOT NULL
SELECT @LASTSTART = MAX (STARTTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @CONTACTSCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @CLOSEDCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 1
SELECT @LASTEND = MAX (ENDTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @NEVERDIALED = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND LASTOUTCOME IS NULL
SELECT @DIALOUTS = SUM(MAXRETRIES) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @SUCCESSCOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @CBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NULL
SELECT @SUCCESSBO = COUNT(OBD_AGENTOUTCOME.ContactID) FROM OBD_AGENTOUTCOME JOIN CONTACT ON CONTACT.CONTACTID = OBD_AGENTOUTCOME.ContactID AND CONTACT.STATUS = 1 JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 1 WHERE OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID
SELECT @FAILEDBO = COUNT(ContactID) FROM OBD_AGENTOUTCOME JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 0 AND OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID
SELECT @VOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,01,02,03,04,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)
SELECT @SUCCESSVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,04)
SELECT @FAILEDVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (01,02,03,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)
END
Hi friends, am completely new to sp..I just know the basic syntax of sp.. i need the above sp in a more efficient manner. Several output variables are passed but with several "select" queries. i want "case-end" to be implemented to achieve the above sp by grouping the similar conditioned queries into a single statement. can anyone please help me out??? any help would be appreciated. Thanks in advance and expecting ur reply!
Thanks,
Nithya S
(
@CAMPAIGNID VARCHAR(64),
@LISTCOUNT INTEGER OUTPUT,
@OPENCOUNT INTEGER OUTPUT,
@PCBCOUNT INTEGER OUTPUT,
@LASTSTART DATETIME OUTPUT,
@CONTACTSCOUNT INTEGER OUTPUT,
@CLOSEDCOUNT INTEGER OUTPUT,
@LASTEND DATETIME OUTPUT,
@NEVERDIALED INTEGER OUTPUT,
@DIALOUTS INTEGER OUTPUT,
@SUCCESSCOUNT INTEGER OUTPUT,
@CBCOUNT INTEGER OUTPUT,
@SUCCESSBO INTEGER OUTPUT,
@FAILEDBO INTEGER OUTPUT,
@VOICEOUTCOMECOUNT INTEGER OUTPUT,
@SUCCESSVOICEOUTCOMECOUNT INTEGER OUTPUT,
@FAILEDVOICEOUTCOMECOUNT INTEGER OUTPUT
)
AS
BEGIN
SELECT @LISTCOUNT = COUNT(DISTINCT(LISTID)) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @OPENCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 0
SELECT @PCBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NOT NULL
SELECT @LASTSTART = MAX (STARTTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @CONTACTSCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @CLOSEDCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND STATUS = 1
SELECT @LASTEND = MAX (ENDTIME) FROM OBD_CAMPAIGNSESSION WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @NEVERDIALED = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND LASTOUTCOME IS NULL
SELECT @DIALOUTS = SUM(MAXRETRIES) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @SUCCESSCOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID
SELECT @CBCOUNT = COUNT(CONTACTID) FROM CONTACT WHERE CAMPAIGNID = @CAMPAIGNID AND CALLTYPE = 1 AND USERID IS NULL
SELECT @SUCCESSBO = COUNT(OBD_AGENTOUTCOME.ContactID) FROM OBD_AGENTOUTCOME JOIN CONTACT ON CONTACT.CONTACTID = OBD_AGENTOUTCOME.ContactID AND CONTACT.STATUS = 1 JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 1 WHERE OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID
SELECT @FAILEDBO = COUNT(ContactID) FROM OBD_AGENTOUTCOME JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_AGENTOUTCOME.Outcome AND OBD_OUTCOME.BUSINESSOUTCOME = 0 AND OBD_AGENTOUTCOME.CAMPAIGNID = @CAMPAIGNID
SELECT @VOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,01,02,03,04,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)
SELECT @SUCCESSVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (00,04)
SELECT @FAILEDVOICEOUTCOMECOUNT = COUNT(CALLOUTCOME) FROM OBD_CALLACTIVITY WHERE CAMPAIGNID = @CAMPAIGNID AND CALLOUTCOME IN (01,02,03,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25)
END
Hi friends, am completely new to sp..I just know the basic syntax of sp.. i need the above sp in a more efficient manner. Several output variables are passed but with several "select" queries. i want "case-end" to be implemented to achieve the above sp by grouping the similar conditioned queries into a single statement. can anyone please help me out??? any help would be appreciated. Thanks in advance and expecting ur reply!
Thanks,
Nithya S