none
sql server query get a list of a table

    Question

  • Hi guys,

    I've a hard query to manage ...

    I need to get for a report the number of required people to an appointment, but this query works but just for the regarding person related to the appointment.

    SELECT 
    
    	CRMAF_FilteredContact.OwnerID
    
    	,CRMAF_FilteredContact.OwnerIDname
    
        ,SU.businessunitidname
    
        ,SU.eu_reporthubname
    
        -- ,SU.eu_reportcountryidname
    
    	,CRMAF_FilteredContact.ContactID
    
    	,CRMAF_FilteredContact.FullName
    
    	,CRMAF_FilteredContact.invoke_tiername
    
    	,CRMAF_FilteredAppointment.ActivityID
    
    	,CRMAF_FilteredAppointment.RegardingObjectID
    
    	,CRMAF_FilteredAppointment.Subject
    
    	,AP.ActivityPartyID
    
    	,AP.PartyID
    
    	,case when CRMAF_FilteredAppointment.ActivityID is null
    
    		then null
    
    		else CRMAF_FilteredContact.ContactID
    
    	 end as s_contactsvisited							-- # of contactsvisited
    
    
    
    FROM FilteredContact CRMAF_FilteredContact
    
    
    
      JOIN FilteredSystemUser SU 
    
      ON CRMAF_FilteredContact.ownerid = SU.SystemUserID
    
    
    
    <strong>  LEFT JOIN FilteredActivityParty AP 
    
      ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId)
    
      AND AP.participationtypemask = '8'     -- regarding</strong> 
    
    
    
      LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment 
    
      ON AP.ActivityID = CRMAF_FilteredAppointment.ActivityID 
    
        AND CRMAF_FilteredAppointment.ownerid = SU.SystemUserID
    
        AND CRMAF_FilteredAppointment.statecode = 1    --complete
    
    
    
    WHERE SU.eu_securityrole like '%RAM%' 
    
    
    
    ORDER BY CRMAF_FilteredContact.OwnerIDname
    
    

    So partyID = contactID

    I get the contactID as this is the current user.

    So the table Filtered_Activity_Party is like below

    Filtered_Activity_Party (idactivity, partyid)

    so something like

    idactivity, partyid
    1  1
    1  20 
    1  3  
    1  10
    2  11
    2  12
    2  3

    I will need in this query to get for the partyId 20 have 1,20,30,10

    So I did something like

    SELECT 
    
    	CRMAF_FilteredContact.OwnerID
    
    	,CRMAF_FilteredContact.OwnerIDname
    
    	,CRMAF_FilteredContact.ContactID
    
    	,CRMAF_FilteredContact.FullName
    
    	,CRMAF_FilteredContact.invoke_tiername
    
    	,AP.ActivityPartyID
    
    	,AP.PartyID
    
    
    
    FROM FilteredContact CRMAF_FilteredContact
    
    
    
    	JOIN FilteredActivityParty AP
    
    		ON (AP.activityid in (
    
    			select activityid
    
    				from FilteredContact CRMAF_FilteredContact
    
    					LEFT JOIN FilteredActivityParty AP 
    
    					ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId)
    
    					where CRMAF_FilteredContact.OwnerIDname like '%testUserName%' and CRMAF_FilteredContact.FullName = 'newcontacttest'
    
    			))
    
    
    
    where CRMAF_FilteredContact.OwnerIDname like '%testUserName%' and CRMAF_FilteredContact.FullName = 'newcontacttest'
    
    ORDER BY CRMAF_FilteredContact.OwnerIDname
    
    

    Which works but I dont know how to implement it in the database, because, there I do a test on a certain user, but then how to link them...

    Thanks SOO MUCH

    Wednesday, August 25, 2010 12:26 PM

Answers

  • To ALL,

    To get quick assistance at this MSDN forum, do the following:

    1. Post CREATE TABLEs for all tables involved

    2. Post 10-20 INSERT INTOs to populate each table, it can be dummy data

    3. Detailed requirements and current queries (if any)

    Thanks.


    Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
    • Proposed as answer by Naomi NModerator Tuesday, August 31, 2010 4:05 PM
    • Marked as answer by KJian_ Wednesday, September 1, 2010 6:45 AM
    Tuesday, August 31, 2010 3:45 PM
    Moderator