STORED pROCEDURE

• Niths

  Old Hand

  Points: 350

  More actions

  June 2, 2010 at 12:08 am

  #387058

  SELECT @SUCCESSBO = COUNT(CASE WHEN CONTACT.STATUS = 1 AND OBD_OUTCOME.BUSINESSOUTCOME = 1 THEN OBD_CALLACTIVITY.ContactID ELSE NULL END),

  @FAILEDBO = COUNT(CASE WHEN OBD_OUTCOME.BUSINESSOUTCOME = 0 THEN OBD_CALLACTIVITY.ContactID ELSE NULL END)

  FROM OBD_CALLACTIVITY

  JOIN CONTACT ON CONTACT.CONTACTID = OBD_CALLACTIVITY.ContactID AND CONTACT.LISTID = @LISTID

  JOIN OBD_OUTCOME ON OBD_OUTCOME.OUTCOMEID = OBD_CALLACTIVITY.CALLOUTCOME

  WHERE OBD_CALLACTIVITY.CAMPAIGNID = @CAMPAIGNID AND OBD_CALLACTIVITY.CALLOUTCOME > 100

  SELECT

  @SUCCESSCOUNT = SUM(CASE WHEN CALLOUTCOME IS NOT NULL AND CALLOUTCOME <> 26 THEN 1 ELSE 0 END),

  @VOICEOUTCOMECOUNT = SUM(CASE WHEN CALLOUTCOME IS NOT NULL

  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) THEN 1 ELSE 0 END),

  @SUCCESSVOICEOUTCOMECOUNT = SUM(CASE WHEN CALLOUTCOME IS NOT NULL

  AND CALLOUTCOME IN (00,04) THEN 1 ELSE 0 END),

  @FAILEDVOICEOUTCOMECOUNT = SUM(CASE WHEN CALLOUTCOME IS NOT NULL

  AND CALLOUTCOME IN (01,02,03,05,06,07,08,10,11,12,13,14,15,16,17,18,19,20,21,24,25) THEN 1 ELSE 0 END)

  FROM OBD_CALLACTIVITY

  JOIN CONTACT ON OBD_CALLACTIVITY.CONTACTID = CONTACT.CONTACTID AND CONTACT.LISTID = @LISTID

  WHERE OBD_CALLACTIVITY.CAMPAIGNID = @CAMPAIGNID

  "The above 2 select queries are taking valus from a single table but with diff where conditions and diff Joins... Is there any possibility of merging these 2 select queries into a single one??? Kindly help... Thanks in Advance! "

  ------------------------
  ~Niths~
  Hard Work never Fails 🙂