Extending SQL query to include records beyond a given date.

Question

I have a query limited by a date range:

select * from mytable 
where COMPLETIONDATE >= TO_DATE('29/06/08','DD/MM/YY')    
and COMPLETIONDATE <= TO_DATE('29/06/09','DD/MM/YY')

The table is a log for activities for a ticket system. The content may be something like:

ticket_id|activity|completiondate
 1    1       some
 1    2       some
 1    3       some
 1    4       some

 2    1       some
 2    2       some
 2    3       some
 2    4       some

That way I know when each activity was completed.

My problems is, if the completiondate of the first activity happens "before" the date range I loss that info:

where completiondate >= 1 jul 09 and completiondate <= 30 jul 09

ticket_id |activity|completiondate
123    3        1 jul 09
123    4        2 jul 09

In this case I have lost the dates for activity 1 and 2 which took place on june 30 or before.

How can I, at the same time limit the date range of the items I want to show, but also include dates from the same tickets beyond the date range?

This is for a tickets report so I have to see:

Tickets from jun 1 - 30 : 

Page won't load, received: march 20, fixed: jun 15
Change color, received: jun 5, fixed:  in progress...

etc.

Answer 1

You're going to need to write a subquery that gets all IDs that have a completion date inside of your range, and then plug that into a query that returns all ticket information for those IDs.

select * 
from mytable
where id in (select id from mytable where 
              COMPLETIONDATE >= TO_DATE('29/06/08','DD/MM/YY')    
              COMPLETIONDATE <= TO_DATE('29/06/09','DD/MM/YY')
)

Answer 2

So you want all records for which at least one activity occurred in the period:

SELECT * 
    FROM mytable 
    WHERE id IN (
       SELECT id 
       FROM mystable 
       WHERE COMPLETIONDATE BETWEEN ... AND ...);

Answer 3

Use a subselect to get the list of ticket IDs to show, filtering the subselect by the date range, then the outer query will be filtered by the ticket IDs returned by the subselect rather than by the daterange