function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Jeff McKinnis 4Jeff McKinnis 4 

joined query syntax

I need help with joined query syntax.

If Resource (pse__Resource__c) has logged billable hours on client projects (pse__Project__r.Account_18_Char_ID__c) within the last 12 months,
return the number of continuing education hours logged during the last 12 months for each resource.

Source Object: 
pse__Timecard__c

Data Fields Needed: 
Resource: pse__Resource__c, pse__Resource__r.FirstName, pse__Resource__r.LastName

Total Hours: pse__Total_Hours__c

Client Project Filter Criteria:
Billable: pse__Billable__c = true
End Date: pse__End_Date__c = LAST_N_MONTHS:12

Continuing Education Filter Criteria: (All CE hours are logged to our account '001G000000oviiRIAQ')
Project Account ID: pse__Project__r.Account_18_Char_ID__c = '001G000000oviiRIAQ'
End Date: pse__End_Date__c = LAST_N_MONTHS:12
Milestone Name:  pse__Milestone__r.Name = 'Coding Compliance Review' OR pse__Milestone__r.Name = 'Other Continuing Education' OR pse__Milestone__r.Name = 'Meetings'


I currently have 2 different queries that work individually.

(Query - 1) This query is currently working to pull CE Hours in last 12 Months:

SELECT  pse__Resource__c, pse__Project__r.Account_18_Char_ID__c, pse__Total_Hours__c, pse__End_Date__c, pse__Milestone__r.Name, pse__Resource__r.FirstName, pse__Resource__r.LastName 
FROM pse__Timecard__c
WHERE pse__Project__r.Account_18_Char_ID__c = '001G000000oviiRIAQ' AND pse__End_Date__c = LAST_N_MONTHS:12 AND  (pse__Milestone__r.Name = 'Coding Compliance Review' OR pse__Milestone__r.Name = 'Other Continuing Education' OR pse__Milestone__r.Name = 'Meetings')

(Query - 2) This query is currently working to pull Resources who whave logged billable hours against a particular client project(s) in the last 12 months:

SELECT  pse__Resource__c, pse__Project__r.Account_18_Char_ID__c, pse__Total_Hours__c, pse__End_Date__c
FROM pse__Timecard__c
WHERE pse__Project__r.Account_18_Char_ID__c = '001G000001laKqrIAE' AND pse__End_Date__c = LAST_N_MONTHS:12 AND pse__Billable__c = true

Again, I am looking for the number of CE hours for any resource who has logged time to a given client's projects by limiting the results of Query 1 by the matching results of Query 2 (matching on pse__Resource__c).
Akhil ReddyAkhil Reddy
SELECT  pse__Resource__c, pse__Project__r.Account_18_Char_ID__c, pse__Total_Hours__c, pse__End_Date__c, pse__Milestone__r.Name, pse__Resource__r.FirstName, pse__Resource__r.LastName 
FROM pse__Timecard__c
WHERE (pse__Project__r.Account_18_Char_ID__c = '001G000000oviiRIAQ' OR pse__Project__r.Account_18_Char_ID__c = '001G000001laKqrIAE') AND pse__End_Date__c = LAST_N_MONTHS:12 AND  (pse__Milestone__r.Name = 'Coding Compliance Review' OR pse__Milestone__r.Name = 'Other Continuing Education' OR pse__Milestone__r.Name = 'Meetings') AND pse__Billable__c = true

The above query will pull all resorce who worked on 001G000000oviiRIAQ or 001G000001laKqrIAE resources in last12 months and Billable is true
Jeff McKinnis 4Jeff McKinnis 4
Thank you for your reply!

However, the query returned no records.  I know there are results for both working queries.  I am currently running them seperately, exporting to Excel to 2 different worksheets and running a vlookup colum to pull the CE hours and displaying in a pivot table. I can also do this with a joined report.

The CE hours are always logged to 001G000000oviiRIAQ these items will always be non-billable and are found by the name. This query returns all hours that were logged as continuing education (not client specific resources).  The billable items will be on the client projects. So there is a subset of all of our resources that also work on the client's project(s).  The purpose is to show the number of continuing education hours that are logged by the specific people who work on a particular client's project(s).
Akhil ReddyAkhil Reddy
Ohh Ok, in this case what you can do is query all resource from Both 001G000000oviiRIAQ and 001G000001laKqrIAE in single query and loop through them and put them in map <pse__Resource__c, List <pse__Timecard__c>>.
 
SELECT  pse__Resource__c, pse__Project__r.Account_18_Char_ID__c, pse__Total_Hours__c, pse__End_Date__c, pse__Milestone__r.Name, pse__Resource__r.FirstName, pse__Resource__r.LastName 
FROM pse__Timecard__c
WHERE (pse__Project__r.Account_18_Char_ID__c = '001G000000oviiRIAQ' OR pse__Project__r.Account_18_Char_ID__c = '001G000001laKqrIAE')

 
Jeff McKinnis 4Jeff McKinnis 4
Maybe a bit closeer, but I am probably not explaining myself very well.  I will try to illustrate it this way:

Part 1 - Find all continuing education hours logged by our employees (resources) in the last 12 months.  Query 1 from above works.  Returns 2,673 records

Part 2 - Who logged billable hours to a particular client in the last 12 months.  Query 2 from above works.  Returns 1,195 records.

Part 3  I am having trouble with.  Both queries return an ID for pse__Resource__c.   For my final output I only want a list of resources where there is a match for a pse__Resource__c along with their hours from Part 1.  This will show how many continuing education hours were logged by resources working on the client's project(s).

I certainly appreciate your willingness to help!!  Thank you!

 
Akhil ReddyAkhil Reddy
Ok, 
Part 3,

1. you loop through all returned results from query and store pse__Resource__c  which pse__Project__r.Account_18_Char_ID__c = '001G000000oviiRIAQ in a set<id>1
2. And loop through first results again with which pse__Project__r.Account_18_Char_ID__c =001G000000oviiRIAQ and store them in second set<id>2
3. now create nested loop
Set1 for query 1
Set2 for query 2
Set3 for common resources to found in two sets 
for (id l1: set1){
    set2.contains(l1){
        set3.add(l1)
    }
}

 
Jeff McKinnis 4Jeff McKinnis 4
My original hope was that I could construct a single query to run to produce a single set of results.  From what I am gathering from your answer, this cannot be done, correct?  I am running the query from Conga Composer with output to an Excel file for use in client presentations.  I am limited to 2,500 rows of output.

Again, thank you very much.  I appreciate your help!