0

I am just wondering if I could write a SOQL query for this:

  • Find list of Lead

    • That have an open Task assigned to specific User and
    • Where the Lead.CreatedDate equals the Task.CreatedDate?
  • are you willing to do this in apex? – cropredy Feb 6 '16 at 1:03
  • 2
    You realize the CreatedDate fields are actually of type Datetime and hence extremely unlikely to collide? – Adrian Larson Feb 6 '16 at 1:05
1

If you are willing to add some architecture you can enable this with a simple checkbox on Lead called something along the lines of Has_Task_Created_Same_Day__c. Then you would write a Task trigger or maybe use Process Builder to build a flow. That trigger or flow would just have to look at records created by your special User with a WhatId corresponds to a Lead, and see if the Lead.CreatedDate was today.

If you want it now, you can't accomplish what you want with pure SOQL; you will need to do some post-processing. You can find all relevant Task records and generate a Map<Id, Set<Date>> corresponding to theCreatedDate values (Date part only). Once you have that you can compare with your Lead records.

Here is some untested code to give you the gist:

static final User SPECIAL_USER; // lazy load someone via query
public static List<Lead> getSomeLeadSubset()
{
    List<Lead> subset = new List<Lead>();
    Map<Id, Set<Date>> taskIdToCreatedDates = getTaskCreatedDates(getLeadIdsWithTask());
    for (Lead lead : [
        SELECT CreatedDate FROM Lead WHERE Id IN :taskIdToCreatedDates.keySet()
    ]){
        Set<Date> taskCreatedDates = new Set<Date>();
        if (taskCreatedDates != null && taskCreatedDates.contains(lead.CreatedDate.date()))
            subset.add(lead);
    }
    return subset;
}
public static Set<Id> getLeadIdsWithSpecialUserTask()
{
    Set<Id> leadIds = new Set<Id>();
    for (AggregateResult aggregate : [
        SELECT WhatId FROM Task WHERE What.Type = 'Lead'
        AND OwnerId = :SPECIAL_USER.Id GROUP BY WhatId
    ]){
        leadIds.add(aggregate.get('WhatId'));
    }
    return leadIds;
}
public static Map<Id, Set<Date>> getTaskCreatedDates(Set<Id> leadIds)
{
    Map<Id, Set<Date>> taskIdToCreatedDates = new Map<Id, Set<Date>>();
    for (Task task : [SELECT WhatId, CreatedDate FROM Task WHERE WhatId IN :leadIds])
    {
        if (!taskIdToCreatedDates.containsKey(task.WhatId))
            taskIdToCreatedDates.put(task.WhatId, new Set<Date>());
        taskIdToCreatedDates.get(task.WhatId).add(task.CreatedDate.date());
    }
    return taskIdToCreatedDates;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.