5/15/2024 2:34:26 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.
|
|
|
|
Contacts not called in past 30 days.
Posted: 15 Dec 06 9:17 AM
|
fiogf49gjkf0d I'm creating a group in contacts. I'm searching for all of them that have an attachment with a certain file name, but I also want to eliminate those from the group that have been called since september 5th, 2006. The best I came up with so far was this:
SELECT DISTINCT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, NameLF
NameLF, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE,
A1.WORKPHONE, A1.MOBILE, A1.EMAIL, A1.ACCOUNTMANAGERID,
A1.SECCODEID, A1.LASTNAME_UC FROM CONTACT A1 INNER JOIN ACCOUNT
A4 ON (A1.ACCOUNTID=A4.ACCOUNTID) INNER JOIN ADDRESS A2 ON
(A1.ADDRESSID=A2.ADDRESSID) LEFT OUTER JOIN ATTACHMENT A3 ON
(A1.CONTACTID=A3.CONTACTID) WHERE (((UPPER(A2.STATE) LIKE
'NV%') OR (UPPER(A2.STATE)='CA') OR (UPPER(A2.STATE) LIKE
'OR%') OR (UPPER(A2.STATE) LIKE 'WA%')) AND
((A3.ATTACHDATE>:DateTime0) AND ((UPPER(A3.FILENAME) LIKE
'%PRORPOSAL%') OR (UPPER(A3.FILENAME) LIKE '%PROP%') OR
(UPPER(A3.FILENAME) LIKE '%PRO MERGE%') OR (UPPER(A3.FILENAME)
LIKE '%PRO MERGE%')))) AND NOT ((A4.ACCOUNTID IN (select
accountid from history where (history.type = 262148 and
history.completeddate > '20060905'))) OR (A1.ACCOUNTID IN
(select accountid from history where (history.type = 262146 and
history.completeddate > '20060905')))) ORDER BY A2.STATE ASC,
A1.LASTNAME_UC ASC, A1.FIRSTNAME ASC
That works pretty well except that it eliminates any contacts with an note in the account. I want it to eliminate any contacts with a note attached to the actual contact, but when I try this next Query it doesn't return any results:
SELECT DISTINCT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, NameLF
NameLF, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE,
A1.WORKPHONE, A1.MOBILE, A1.EMAIL, A1.ACCOUNTMANAGERID,
A1.SECCODEID, A1.LASTNAME_UC FROM CONTACT A1 INNER JOIN ADDRESS
A2 ON (A1.ADDRESSID=A2.ADDRESSID) LEFT OUTER JOIN ATTACHMENT A3
ON (A1.CONTACTID=A3.CONTACTID) WHERE (((UPPER(A2.STATE) LIKE
'NV%') OR (UPPER(A2.STATE)='CA') OR (UPPER(A2.STATE) LIKE
'OR%') OR (UPPER(A2.STATE) LIKE 'WA%')) AND
((A3.ATTACHDATE>:DateTime0) AND ((UPPER(A3.FILENAME) LIKE
'%PRORPOSAL%') OR (UPPER(A3.FILENAME) LIKE '%PROP%') OR
(UPPER(A3.FILENAME) LIKE '%PRO MERGE%') OR (UPPER(A3.FILENAME)
LIKE '%PRO MERGE%')))) AND NOT ((A1.CONTACTID IN (select
contactid from history where (history.type = 262148 and
history.completeddate > '20060905'))) OR (A1.CONTACTID IN
(select contactid from history where (history.type = 262146 and
history.completeddate > '20060905')))) ORDER BY A2.STATE ASC,
A1.LASTNAME_UC ASC, A1.FIRSTNAME ASC
Any ideas? Also, instead of specifying a particular date, how do I write the "within the last xx days" into the select contactid condition? Thanks for the help! |
|
|
| |
|
Re: Contacts not called in past 30 days.
Posted: 15 Dec 06 10:02 AM
|
fiogf49gjkf0d Heh, I figured that'd be the question that would be answered first. Thanks though, that helps - I'm new to using query builder and had no previous SQL knowledge so I'm sort of fumbling blindly. |
|
|
|
Re: Contacts not called in past 30 days.
Posted: 15 Dec 06 11:06 AM
|
fiogf49gjkf0d I looked at you 2 SQL statements. Since the first one worked I am assuming that there is a typo somewhere in the second one but I done see it. Anyways, the approach should work. Suggest you recheck everthing.
Questions:
-did you check the "Use as Literal" option? If not you should check it.
-what SLX version and patch level are you running? |
|
|
|
Re: Contacts not called in past 30 days.
Posted: 15 Dec 06 11:14 AM
|
fiogf49gjkf0d I didn't originally "use as literal", but I just tried it and still 0 results (whereas the query using contactid.accountid select accountid returns a dozen or so)
I'm using SLX 7.0, out of the box (well, it has the 2 hotfixes that my business partner used) We just upgraded from ver 6.0, and I had the same problem there. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|