Query to get All Opportunities that between Start and End Date

Question

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?

Answer 1

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.

Answer 2

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)

Answer 3

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!