locked
SQL Query to show service activities RRS feed

  • Question

  • Hi All,

    I'm new here so probably doing this wrong but:

    I'd like an SQL query to bring back the Subject, postcode (from the customer) and the resources name, from a service activity.

    So I can run the query and get all activities, basically who is going where.

    I've been looking at this for ages, given up and joined here. Thanks in advance!

    Tuesday, November 19, 2013 11:19 PM

All replies

  • In the Service activity entity, a customer can be an account or a contact, a Resource can be a systemuser or a facility/equipment.
    The relationship between the Service activity and customer/resources are stored in activityparty.

    So here is some sql snippet that will give you the data you are looking. You will need to refactor/combine this sample sql scripts to give you the final results.
    Hope this helps

    -- Customer details
    select	s.activityid, s.subject, ap.partyobjecttypecode as customerTypeCode, ap.partyidname as customer, 
    		case ap.partyobjecttypecode
    			when 1 then a.address1_postalcode
    			when 2 then c.address1_postalcode
    		end as PostCode
    from	FilteredServiceAppointment s
    		left join FilteredActivityParty ap on s.activityid = ap.activityid and ap.participationtypemaskname = 'Customer'
    		left join FilteredAccount a on ap.partyid = a.accountid
    		left join FilteredContact c on ap.partyid = c.contactid
    
    -- Resources details
    select	s.activityid, s.subject, ap.partyobjecttypecode as resourceTypeCode, ap.partyidname as resources
    from	FilteredServiceAppointment s
    		left join FilteredActivityParty ap on s.activityid = ap.activityid and ap.participationtypemaskname = 'Resource' 


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Wednesday, November 20, 2013 12:05 AM
  • Hi,

    Thanks very much. I can't seem to combine the queries to get the info from the top query but with the resource.

    I pretty much need each row returned to have Subject, postcode, resource.

    Of course it will probably return a row per resource rather than per job, since more than one person could be going.

    I might have to use the SDK for this since it doesn't seem to work like that.

    Wednesday, November 20, 2013 11:11 AM
  • I think this does it, when I get time later I'll check that it is bring back the right data. thanks again for the help.

    select az.partyidname,  s.activityid, s.subject, ap.partyobjecttypecode as customerTypeCode, ap.partyidname as customer, 
    case ap.partyobjecttypecode
    when 1 then a.address1_postalcode
    when 2 then c.address1_postalcode
    end as PostCode
    from FilteredServiceAppointment s
    left join FilteredActivityParty ap on s.activityid = ap.activityid and ap.participationtypemaskname = 'Customer'
    left join FilteredAccount a on ap.partyid = a.accountid
    left join FilteredContact c on ap.partyid = c.contactid
            left join FilteredActivityParty az on az.activityid = ap.activityid and az.participationtypemaskname = 'Resource'

    Wednesday, November 20, 2013 1:37 PM