2

I am writing a report for which I need to break down the monthly revenue. So Given the Report Parameters (Start and End Date). Given an opportunity's start and end date if any of the dates during that duration fall between the given report parameters, It will show up on the list.

Currently I have the following query, but this does not work for all cases:

SELECT Account.Name, Account.Office__c, Name, Start_Date__c, End_Date__c,
        Estimated_Production_Revenue_Won__c FROM Opportunity WHERE (Start_Date__c >= 2015-05-01 AND Start_Date__c <= 2015-07-30) OR (End_Date__c >= 2015-05-01 AND End_Date__c <=2015-07-30)

However, I have an opportunity which started on the 1-01-2015 and ends on 12-31-2015 and my report start and end date parameters are 05-05-2015 and 12-31-2015, this does not show up. How would I modify this query so that in this particular case it would show up as well?

  • I think you may need to clarify your goal here, because your query clearly searches for records with either start_date or end_date between 5/1-7/30, and your other opportunity obviously does not fit those criteria. – RenegadeCoder Jun 16 '15 at 15:18
  • 1
    @RenegadeCoder Please review the edit and let me know if you have any thoughts on how I can do this. – molly_567 Jun 16 '15 at 15:35
2

If you want to find any Opportunity that was active between two dates then you can use the simpler where clause

WHERE Start_Date__c <= 2015-07-30  //Latest date for active Opps
AND End_Date__c >= 2015-05-01      //Earliest date for active Opps

In other words, the Opportunity must have started by the last date you're interested in and finished after the first date you are interested in.

| improve this answer | |
1

if your goal is simply to find every Opportunity that was active between those dates (rather than just starting or ending during that timeframe), you'd want to update your where clause to include a third condition as such:

WHERE (Start_Date__c >= 2015-05-01 AND Start_Date__c <= 2015-07-30) OR (End_Date__c >= 2015-05-01 AND End_Date__c <=2015-07-30) OR (Start_Date__c <=2015-05-01 AND End_Date__c >= 2015-07-30)

| improve this answer | |
  • Thanks, please upvote and mark solution if this solved your issue :) – RenegadeCoder Jun 16 '15 at 16:55
  • @RenegadeCoder I think your solution is perfectly valid, but it uses 6 comparisons when only 2 are needed ;) – Doug B Jun 17 '15 at 8:33
0

Sounds like you need what I was just working on. Something like this:

SELECT Account.Name, Account.Office__c, Name, Start_Date__c,
End_Date__c, Estimated_Production_Revenue_Won__c
FROM Opportunity
WHERE (
   Start >= :startDate
   AND
   End <= :endDate
)
OR
(
   Start >= :startDate
   AND
   Start <= :endDate
   AND
   End >= :endDate
)
OR
(
   Start <= :startDate
   AND
   End >= :endDate
)
OR
(
   Start <= :startDate
   AND
   End >= :startDate
   AND
   End <= :endDate
)

Will flag Opps that start before and end during, start during and end after, start before and end before or start during and end during.

It's a pain and I have had to go back to this a few times!

| improve this answer | |

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.