Posted by Jimbo on 04/20/07 23:03

I have a query..if you look at the bottom of the where clause you'll
see an "NOT IN" statement that is really hanging up the query..i'm
trying to replace with a "NOT EXISTS" but it isnt appearing to
work...I need to get a result set where the email address of the
outter most query is not in that sub query...thanks:

-->Code Begins Here

SELECT
DISTINCT
'5367' AS SURVEYID,
ISNULL(B.EMAIL_ADDR,'') AS C_EMAIL_ADDR,
A.CASE_ID,
A.SITE_ID,
C.SITE_NAME,
CONVERT(VARCHAR(12), A.ROW_ADDED_DTTM, 101) AS C_OPENED_DT,
CONVERT(VARCHAR(12), A.ROW_ADDED_DTTM, 108) AS C_OPENED_TM,
CONVERT(VARCHAR(12), A.CLOSED_DTTM, 101) AS C_CLOSED_DT,
CONVERT(VARCHAR(12), A.CLOSED_DTTM, 108) AS C_CLOSED_TM,
A.RC_STATUS,
A.SOLUTION_ID,
A.RC_SOURCE,
CASE WHEN LEN(ISNULL(A.PRODUCT_GROUP, '')) = 0 THEN '[blank]' ELSE
D.PRODUCT_GROUP_DESCR END AS PRODUCT_GROUP,
B.FIRST_NAME AS C_FIRST_NAME,
B.LAST_NAME AS C_LAST_NAME,
B.TITLE AS C_TITLE,
B.PHONE AS C_PHONE,
CASE WHEN LEN(ISNULL(B.EXTENSION, '')) = 0 THEN '[blank]' ELSE
B.EXTENSION END AS EXTENSION,
CASE WHEN LEN(ISNULL(A.LOCATION, '')) = 0 THEN '[blank]' ELSE
A.LOCATION END AS LOCATION,
CASE WHEN LEN(ISNULL(B.CITY, '')) = 0 THEN '[blank]' ELSE B.CITY END
AS CITY,
CASE WHEN LEN(ISNULL(B.STATE, '')) = 0 THEN '[blank]' ELSE B.STATE
END AS STATE,
CASE WHEN LEN(ISNULL(B.POSTAL, '')) = 0 THEN '[blank]' ELSE B.POSTAL
END AS POSTAL,
CASE WHEN LEN(ISNULL(B.REGION_ID, '')) = 0 THEN '[blank]' ELSE
B.REGION_ID END AS REGION_ID,
CASE WHEN LEN(ISNULL(C.COUNTRY, '')) = 0 THEN '[blank]' ELSE
C.COUNTRY END AS COUNTRY,
CASE WHEN LEN(ISNULL(C.SITE_TYP_ID, '')) = 0 THEN '[blank]' ELSE
C.SITE_TYP_ID END AS SITE_TYPE,
CASE WHEN LEN(ISNULL(A.CASE_TYPE, '')) = 0 THEN '[blank]' ELSE
A.CASE_TYPE END AS CASE_TYPE,
CASE WHEN LEN(ISNULL(A.COMPETENCY, '')) = 0 THEN '[blank]' ELSE
A.COMPETENCY END AS DEVICE_TYPE,
CASE WHEN LEN(ISNULL(A.PROVIDER_GRP_ID, '')) = 0 THEN '[blank]' ELSE
A.PROVIDER_GRP_ID END AS PROVIDER_GRP,
A.CLOSED_BY_OPRID,
A.ROW_ADDED_OPRID,
CASE WHEN LEN(ISNULL(A.PX_LEVEL, '')) = 0 THEN '[blank]' ELSE
A.PX_LEVEL END AS PX_LEVEL,
CASE WHEN LEN(ISNULL(A.RC_PRIORITY, '')) = 0 THEN '[blank]' ELSE
A.RC_PRIORITY END AS PRIORITY,
CASE WHEN LEN(ISNULL(A.RC_SEVERITY, '')) = 0 THEN '[blank]' ELSE
A.RC_SEVERITY END AS SEVERITY,
CASE WHEN LEN(ISNULL(E.SO_ID,'')) = 0 THEN '[blank]' ELSE E.SO_ID END
AS SO_ID,
CASE WHEN LEN(ISNULL(E.EMAIL_ADDR,'')) = 0 THEN '[blank]' ELSE
E.EMAIL_ADDR END AS S_EMAIL_ADDR,
CASE WHEN LEN(ISNULL(E.OPENED_DT,'')) = 0 THEN'[blank]' ELSE
E.OPENED_DT END AS S_OPENED_DT,
CASE WHEN LEN(ISNULL(E.OPENED_TM,'')) = 0 THEN'[blank]' ELSE
E.OPENED_TM END AS S_OPENED_TM,
CASE WHEN LEN(ISNULL(E.CLOSED_DT,'')) = 0 THEN'[blank]' ELSE
E.CLOSED_DT END AS S_CLOSED_DT,
CASE WHEN LEN(ISNULL(E.CLOSED_TM,'')) = 0 THEN'[blank]' ELSE
E.CLOSED_TM END AS S_CLOSED_TM,
CASE WHEN LEN(ISNULL(E.FIRST_NAME,'')) = 0 THEN'[blank]' ELSE
E.FIRST_NAME END AS S_FIRST_NAME,
CASE WHEN LEN(ISNULL(E.LAST_NAME,'')) = 0 THEN'[blank]' ELSE
E.LAST_NAME END AS S_LAST_NAME,
CASE WHEN LEN(ISNULL(E.TITLE,'')) = 0 THEN'[blank]' ELSE E.TITLE END
AS S_TITLE,
CASE WHEN LEN(ISNULL(E.PHONE,'')) = 0 THEN '[blank]' ELSE E.PHONE END
AS S_PHONE,
CASE WHEN LEN(ISNULL(A.RC_SUMMARY,'')) = 0 THEN '[blank]' ELSE
A.RC_SUMMARY END AS CASE_SUMMARY,
CASE WHEN LEN(ISNULL(E.SERIAL_ID,'')) = 0 THEN '[blank]' ELSE
E.SERIAL_ID END AS S_SERIAL_ID,
CASE WHEN LEN(ISNULL(E.CONTACT_PERSON_ID,'')) = 0 THEN '[blank]' ELSE
E.CONTACT_PERSON_ID END AS S_PERSON_ID,
CASE WHEN LEN(ISNULL(A.CASE_CONTACT,'')) = 0 THEN '[blank]' ELSE
A.CASE_CONTACT END AS C_PERSON_ID,
CASE WHEN LEN(ISNULL(E.LOCN,'')) = 0 THEN '[blank]' ELSE E.LOCN END
AS S_LOCN,
CASE WHEN LEN(ISNULL(E.TECH_NAME,'')) = 0 THEN '[blank]' ELSE
E.TECH_NAME END AS S_TECH_NAME,
'N' AS SERVICEORDER_FLG,
CASE WHEN C.STATE = 'QB' THEN 1 ELSE 3 END AS FC_LANGUAGE_FLG
FROM
FCT_CASE A
-->JOINS LU_SITE_REP TO GET THE CASE CONTACT NAME
INNER JOIN
LU_SITE_REP B
ON
A.CASE_CONTACT = B.PERSON_ID
AND A.SITE_ID = B.SITE_ID
-->JOINS DIM_SITE TO GET THE SITE INFORMATION FOR THE CASE
INNER JOIN
DBO.DIM_SITE C
ON
A.SITE_ID = C.SITE_ID
-->LEFT JOIN TO GET THE PRODUCT GROUP NAME IF THE PRODUCT GROUP FOR
THE CASE IS NOT BLANK
LEFT JOIN
LU_PRODUCT_GROUP D
ON
A.PRODUCT_GROUP = D.PRODUCT_GROUP

LEFT JOIN
-->BEGIN DERIVED TABLE E
-->E RETRIEVES ALL THE INFO FROM THE SERVICE ORDER IF A SERVICE ORDER
EXISTS FOR THE CASE
(
SELECT
S.SO_ID,
P.EMAIL_ADDR,
S.CASE_ID,
CONTACT_PERSON_ID,
(
SELECT
FIRST_NAME + ' ' + LAST_NAME
FROM
DIM_WORKER W
WHERE
W.PERSON_ID = S.PERSON_ID

) AS TECH_NAME,

CONVERT(VARCHAR(12), S.ROW_ADDED_DTTM, 101) AS OPENED_DT,
CONVERT(VARCHAR(12), S.ROW_ADDED_DTTM, 108) AS OPENED_TM,
CONVERT(VARCHAR(12), S.PX_COMPLETED_DTTM, 101) AS CLOSED_DT,
CONVERT(VARCHAR(12), S.PX_COMPLETED_DTTM, 108) AS CLOSED_TM,
P.FIRST_NAME,
P.LAST_NAME,
P.TITLE,
P.PHONE,
S.SERIAL_ID,
S.LOCN
FROM
DIM_SO_HDR S,
LU_SITE_REP P
WHERE
S.CONTACT_PERSON_ID = P.PERSON_ID
AND S.CASE_ID <>0
AND S.ROW_ADDED_DTTM BETWEEN DATEADD(dd, DATEDIFF(dd,0,GETDATE()-2),
0) AND DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0)
) E -->END DERIVED TABLE E
ON
A.CASE_ID = E.CASE_ID
WHERE
(
-->RESTRICTIONS FOR RC_STATUS=CLOSR
(ISNULL(A.RC_STATUS,'') = 'CLOSR'AND ISNULL(A.SOLUTION_STATE,'1') =
'1' AND ISNULL(A.SOLUTION_ID,0) NOT IN (319852, 319716, 319825,
319775, 319776) )
-->RESTRICTION FOR RC_STATUS= OPENC
OR(ISNULL(A.RC_STATUS,'')='OPENC')
)
AND CAST(A.SITE_ID AS INT) NOT IN (909, 900, 903)
AND B.EMAIL_ADDR <> 'noc.eon@e.pagenet.ca'
AND B.EMAIL_ADDR NOT LIKE '%cardinal.com'
AND B.EMAIL_ADDR LIKE '%@%'
AND A.CUSTSAT_EXCLUDE_FLG <> 'Y'
AND CASE WHEN LEN(ISNULL(A.PRODUCT_GROUP, '')) = 0 THEN '[blank]'
ELSE D.PRODUCT_GROUP_DESCR END <> 'PYXISSTATION'
AND DATEADD(dd, DATEDIFF(dd,0,A.ROW_ADDED_DTTM), 0) = DATEADD(dd,
DATEDIFF(dd,0,getdate()-1), 0)
-->RESTRICTION THAT ONLY PULLS IN CASES WHERE THE EMAIL ADDRESS HAS
NOT BEEN INCLUDED IN A SURVEY IN THE LAST 30 DAYS
AND B.EMAIL_ADDR NOT IN
(
SELECT
DISTINCT
H.EMAIL_ADDR
FROM
FCT_CASE G,
LU_SITE_REP H
WHERE

G.CASE_CONTACT = H.PERSON_ID
AND(
-->RESTRICTIONS FOR RC_STATUS=CLOSR
(ISNULL(G.RC_STATUS,'') = 'CLOSR'AND ISNULL(G.SOLUTION_STATE,'1') =
'1' AND ISNULL(G.SOLUTION_ID,0) NOT IN (319852, 319716, 319825,
319775, 319776) )
-->RESTRICTION FOR RC_STATUS= OPENC
OR(ISNULL(G.RC_STATUS,'')='OPENC')
)
AND CAST(G.SITE_ID AS INT) NOT IN (909, 900, 903)
AND G.CUSTSAT_EXCLUDE_FLG <> 'Y'
AND DATEADD(dd, DATEDIFF(dd,0,G.ROW_ADDED_DTTM), 0) BETWEEN
DATEADD(dd, DATEDIFF(dd,0,getdate()-30), 0)AND DATEADD(dd, DATEDIFF(dd,
0,getdate()-2), 0)
AND H.EMAIL_ADDR IS NOT NULL

)
ORDER BY A.CASE_ID DESC

[Back to original message]