March 13, 2008 at 4:00 pm
Am writing a report. Have two different tables joined. The two tables are linked based on two different fields/columns. One table is accounts, one table is contacts. There is a link using the accountid. If it is a contact under that account, then the accountid column will match in both tables. There is another link, and thats primarycontactid. If the contact is the primary contact for the account, then the primarycontactid will match the contactid. WHEW. I am sure I did not explain that well, but anywho... what I need to do is list all contacts for the account, and add a column that just says yes or no as far as if they are the primary contact or not. Everything is finished except that column.... I mean I can list the primary contact's name, but it will repeat on every line and I dont think that looks very good just adding a column to my report, that says "Primary Contact Name" and have it repeat with every contact.... need more info, let me know. Thanks guys!
March 13, 2008 at 4:04 pm
Asking for more:
For a better response, please read the following article. It provides excellent guidance on asking for help.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
There are plenty of people out here who would like to help, but you haven't provided enough info to really help at all.
March 13, 2008 at 4:16 pm
Okay.. I apologize I was just a little unsure how to post this since my code is functioning beautifully, I just don't know how to write the specific code that I need to add. This is the query I am using for my report. Bear in mind that I am using Visual Studio to write my reports, so it does automatically format the code a little different than what I am used to. This is a basic piece of code however, so there shouldn't be any trouble understanding it.
--
SELECT crmaf_account.name AS Company, crmaf_account.new_membershiptypename
AS MemberType, crmaf_account.accountcategorycodename AS Category,
crmaf_account.accountclassificationcodename AS Classification,
crmaf_account.primarycontactid
FROM FilteredAccount crmaf_account RIGHT OUTER JOIN
FilteredContact CRMAF_Contact ON crmaf_account.accountid =
CRMAF_Contact.accountid
--
I want all of the contacts to come back. There are many many contacts with some individual accounts, and that is why I did the outer join. What I want to add, is a column on the report that identifies if the contactid for that record is the contactid that is listed in the account table's primarycontactid column for that record. Hopefully that clarifies slightly.
March 13, 2008 at 4:35 pm
Okay, I reformatted the code, but I still don't really know what you are looking for. part of it is I am not sure which table is which that has the id you are concerned about showing.
It might help if we had the DDL for the tables (or at least enough to work with based on the query), some sample data, and what you expect to get in the result set that you would then build the report on.
😎
March 13, 2008 at 4:44 pm
I think I saw the light. Is this what you are trying to get?
SELECT
crmaf_account.name AS Company,
crmaf_account.new_membershiptypename AS MemberType,
crmaf_account.accountcategorycodename AS Category,
crmaf_account.accountclassificationcodename AS Classification,
case when crmaf_account.primarycontactid = CRMAF_Contact.accountid
then 'Yes'
else 'No'
end
FROM
FilteredAccount crmaf_account
RIGHT OUTER JOIN FilteredContact CRMAF_Contact
ON (crmaf_account.accountid = CRMAF_Contact.accountid)
😎
March 13, 2008 at 6:25 pm
Yanno, that is so awesome. I havent had a whole lot of sleep this week.... I thought that it was a case statement I was looking for, but then I thought I had formatted it wrong, then I thought that it just wasn't going to work in that situation... for some reason I didnt think I could use uid's in a case statement but apparently I can. It is exactly what I was looking for. Thanks Lynn!!! I love SSC so frikkin much....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply