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!

Data & Imports Forums - T-SQL & Queries
Forum to discuss general T-SQL questions and help with queries related to SalesLogix data. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to T-SQL & Queries | New Thread

View: Search:

Author

Thread: Help creating contact group for contacts without any records.

Adam Conde
Posts: 13

Help creating contact group for contacts without any records. Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 05 Sep 06 8:14 PM

I would like to create a contact group, that lists only contacts without any history (ie. notes, opportunities, tickets, activites, history, etc...).
Is there a way to do that?

I've been successful in creating this report showing the contacts WITH history, but cannot do the alternate (WITHOUT history).

To do that I set the condition to:
Contact .Contactid in (select distinct a1.contactid from history a1) AND
Contact .Contactid in (select distinct a1.contactid from ticket a1) AND
Contact .Contactid in (select distinct a1.contactid from opportunity a1) AND
Contact .Contactid in (select distinct a1.contactid from activity a1) END

Putting this condition inside NOT() results in an empty recordset.

I basically want to create this group so that I can use it to purge contacts without any history. Perhaps I am going about this all wrong and there is an easier way to accomplish this?

Any ideas?
Thanks in advance!

[Reply][Quote]

Frank Chaffin
Posts: 475

Re: Help creating contact group for contacts without any records. Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 05 Sep 06 9:05 PM

Try a group with these conditions:

Contact .Activity.Activityid (does not contain data) AND
Contact .History.Historyid (does not contain data) AND
Contact .Ticket.Ticketid (does not contain data) AND
Contact .Opportunity_Contact.Opportunity.Opportunityid (does not contain data) END

You will also need change the joins between Contact -> Opportunity_Contact and Opportunuty_Contact -> Opportunity to LEFT joins.

[Reply][Quote]

Adam Conde
Posts: 13

Re: Help creating contact group for contacts without any records. Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 06 Sep 06 1:34 AM

excellent! that worked perfectly.

thank you very much!

[Reply][Quote]

Page 1 of 1

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!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 11/25/2024 5:10:04 PM