function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
JoeSpecJoeSpec 

SOQL trouble

I've been having a lot of difficulty gettting the data i want from a soql query.  First I was having trouble getting it from a report so gave up thinking soql would be more robust, but it seems to thwart me at every turn.

I have the following 3 objects  contacts,  Cases__c, DirectServices__c  (note taht cases is custom obj)   

 

Cases - > DirectService is Master/Detail.

Cases and DirectSerivices both have contact lookup

 

I want to get results from soql for how i would assume this would work :

 

Select contact__c, contact__r.Client_Full_Name__c, Owner.Name   , (select sum(total_time__c) from direct_service__r where date <x and date >y) from case__c  where case__c.Service = 'xxx' and date<x and date>y

 

But it won't let me use aggregate function in the subquery.  and ultimately I actually want a sum of all direct services  on any case over that time period for a client who had an open case of service 'xxx;  not actually the direct_service__r relationship which is just Direct_Services for cases with service 'xxx'.

 

I also tried it from the other direction doing a select on the direct_service and  grouping by the contact and doing a Contact_c in (select contact__c from case__C where ....)

 

something like :

SELECT  MIN(Client__r.Client_Full_Name__c) Client_Name,  MIN(case__r.HSA_Worker_Name__c) HSA_Worker_Name,  MIN(case__r.HSA_Worker_ID__c) HSA_Worker_ID ,MIN(Case__r.CalWORKS_Case_Number__c) Calworks_Case_Number ,  MIN(case__r.Owner.Name) Owner, MIN(case__r.open_date__c) Open_Date ,  MIN(case__r.close_date__c) Close_Date, sum(Total_Time__c) Total_Time   
FROM Direct_Service__c  
WHERE Client__c  in 
    (Select contact__c from case__c 
           WHERE (service__c = 'a1R20000000xxxx' AND  open_date__c<= 2013-08-31) 
           AND (status__c='Case Open'  or (Status__c='Case Closed' and Close_date__c >= 2013-08-31 )))  
AND Location__c != 'Phone' AND Direct_Service_Type__c != 'Collateral'
group by Client__c 

 

 

This almost worked but it turns out the MIN(Case__r....) data isn't what I want.  I actually want it for the specific service__c, and since multiple cases can get returned ...

also what I really want is all cases not just those with DirectService entries,  if no direct service entries it should return 0 total time.

 

 

JoeSpecJoeSpec

Oh one other thing.  I could get the data I'm looking for in a report, with a sum of the grouping for total time, but this needs to be exported to excel for a larger process so I need one row per client in the export.

 

I'm expecting It's just not possible, but frustrating since in sql it would pretty easy, and I could get the data in many different ways, here every way I'm trying doesn't quite allow me to do what I want.

 

 
ericmonteericmonte

JoeSpec

 

Can you clarify something for me? You want to be able to Count all the Direct Service with a Case and services = xxx? Is this correct? If you can clarify what you really want I can probably help on your query.
ericmonteericmonte

After rereading your post i think i found your answer.

 

Integer countDirectServices;

 

List <Case> ds = [select id, Name from Case where ID in (Select Case from Direct Services and open date = date)];

 

countDirectServices = ds.size();

 

 

 

 

 

Actually this wont do it i read your post wrong again... argh.. let me take a look at this again because it looks like you need the following information:

 

Direct Services with Clients from a particular case.

 

So i think you should do the following:

 

Query the Case record to retrive all the contacts and put this into a list, name it cList.

 

Then in your directServices query you can do Sum(TotatlTime__c) from Direct Services where Client__c in: (cList);

 

Hopefull i capture your requirement correctly.

 

 

 
JoeSpecJoeSpec

Hey Eric, 

Many thanks for the reply.

I will clarify, but I was trying to do this outside of APEX which I'm not very familiar with.  That of course may not be possible.

As i was saying i want 1 row per client so i can export it into a spreadsheet for some further processing.

 

In mostly english:

I'm trying to get the sum(Total_Time)  for all DirectServices  for a contact who has a case open during the month of type/service ='xxx'.

the sum should be of all DirectServices of that client during the month, not just those related to case 'xxx'

 

The row also needs to include some info from the case 'xxx'   and some info from the contact.

 

does that make sense?

 
ericmonteericmonte
It makes sense. Let me get back to you on this. I have a solution with the SOQL but for some odd reason my brain is fried.
ericmonteericmonte

Okay I think i got it.

 

First what you have to do is create an empty List for all the Contact

 

List <Contact> clist = new List <Contact>();

 

then do a for loop to retrieve all the Contacts that has Case.

 

For( Contact c : [select id from Contact where ID IN ( select contactId from Case where services = 'xxxxx' and open date <= ....)]{

//so the for loop basically gets all those contacts id from your case criteria then we take the Contact Id and it to the empty List.

clist. add(c);

}

 

Since now you have all the ids for the contact you can now do an aggregate functions to get the Total Time 

 

AggregateResult[] aggrResult = [select sum(Total_Time__c) from Direc_Services__c where Client__c in: (cList)];

 

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

 
JoeSpecJoeSpec

Thanks again Eric,

I'm guessing that's what I'll have to do.

I was hoping to do it in a single SOQL statement that I could run outside of APEX and export to excel,
ericmonteericmonte
Unfortunately this will require soql, I was thinking roll up summaries but u need master detail objects.