How to Select data for the last 2 quarters from database using SQL?

Question

I have data of the models for the last 2 years in our database. I want to select only those models from the database table for the last 2 Quarters.

Suppose, Sysdate-> 29/07/2013(July)

I should be able to retrieve the models from the month of below quarters

Q1-April,May,June(last quarter)

Q2-July,Aug,Sep(Including quarter)

How does the below query need to change?

Select model_id,model_name,Effective_date_from,effective_date_to 
from   model 
where active='YES';

Answer 1

UPDATE

For Oracle:

FROM mytable t
JOIN ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'Q'),3) AS b
            , ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3) AS e
         FROM DUAL
     ) dr
  ON NOT ( t.effective_date_from >= dr.e OR t.effective_date_to < dr.b )
  OR ( t.effective_date_from IS NULL AND t.effective_date_to IS NULL )

For MySQL:

FROM mytable t
JOIN ( SELECT MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER AS b
            , MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER AS e
     ) dr
  ON NOT ( t.effective_date_from >= dr.e OR t.effective_date_to < dr.b )
  OR ( t.effective_date_from IS NULL AND t.effective_date_to IS NULL )

---

After reviewing the answer from slOppy and reviewing the question again, I realized that you have two date columns, effective_date_from and effective_date_to, and that I did not take into account that you are probably wanting to retrieve rows where any point in time between the from and to dates values falls any time in the specified date range (between the beginning of the previous quarter and the beginning of the next quarter.)

To simplify (for now), I'm going to consider only cases where the effective date from < to.

The possible cases can be depicted (rather crudely) something like this: The vertical bars represent drb and dre (the date range begin and end) The less than sign represents "effective_date_from" (edf) The greater than sign represents "effective_date_to" (edt) The dashes represent the "effective" dates between edf and edt

      drb     dre
<--->  |       |              case 1: edt < drb
   <---|---->  |              case 2: drb between edf and edt
    <--|-------|---->         case 3: edf < drb AND edt > dre
       | <---> |              case 4: edf > drb AND edt < dre
       |    <--|--->          case 5: edf between drb and dre AND edt > dre
       |       |  <--->       case 6: edf > dre
       |       |
   <--->       |              case e1: edt = drb
    <--|------->              case e2: edf > drb AND edt = dre
       <--->   |              case e3: edf = drb and edt < dre
       <------->              case e4: edf = drb and edt = dre
       <-------|-->           case e5: edf = drb and edt > dre
       |   <--->              case e6: edf > drb AND edt = dre
       |       <--->          case e7: edf = dre

I think you are asking to return rows that satisfy cases 2 thru 5 and the edge cases e1 thru e6, which is all cases EXCEPT for case 1, 6 and e7.

If we can write a predicate that tests for cases 1 and 6, and then negate that, it should give us what you want. Something like this:

To handle datetime with time component considered:

  WHERE NOT ( effective_date_to < drb OR effective_date_from >= dre )

(Expressions to generate drb and dre (date range begin and date range end) are shown in my original answer.)

To also return rows where both effective_date_from and effective_date_to are NULL, we can add a separate condition to handle that case.

Using an inline view to provide those expressions, we get the SQL at the top of the answer.

I previously shelved the oddball cases, such as effective_date_from > effective_date_to; you have to figure out if those rows are valid, and the conditions when those should be returned.

---

ORIGINAL ANSWER:

This can be achieved in both Oracle and MySQL. The approach I use is to first generate the "begin date" of each quarter, relative to the current date. We can reference those date values (or expressions which return date values) in a predicate.

To derive the "begin date" of a quarter in MySQL, we can make use of the QUARTER() function. (This function returns an integer value 1 through 4 from a specified DATE value. In combination with the YEAR and MAKEDATE functions, we can generate the "begin date" of each relative quarter from the current system date, like this:

SELECT MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER AS next_q
     , MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-1 QUARTER AS this_q
     , MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER AS prev_q

       next_q      this_q      prev_q      
       ----------  ----------  ------------
       2013-10-01  2013-07-01  2013-04-01  

Once we have those date values, it's straightforward. To get only the rows where a particular date column (mydate in the following example) has a value that falls within the previous or current quarter, we just compare that date column with two of the expressions above:

WHERE mydate >= MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER
  AND mydate <  MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER

---

In Oracle, the approach is the same. The difference is in how we derive the begin dates of the relative quarters. In Oracle, we can use expressions like these:

-- ALTER SESSION SET nls_date_format = 'YYYY-MM-DD'; 

SELECT ADD_MONTHS(TRUNC(SYSDATE,'Q'),3) AS next_q
     , ADD_MONTHS(TRUNC(SYSDATE,'Q'),0) AS this_q
     , ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3) AS prev_q
  FROM DUAL

        NEXT_Q      THIS_Q      PREV_Q
        ----------  ----------  ------------
        2013-10-01  2013-07-01  2013-04-01  

So, to get rows where the value of mydate is in the current or previous quarter, we compare the column with two of those expressions:

WHERE mydate >= ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3)
  AND mydate <  ADD_MONTHS(TRUNC(SYSDATE,'Q'),3)

(greater than or equal to the begin date of the previous quarter, and less than the begin date of the next quarter).

---

A slightly different way to use those "begin dates" is to use an inline view to return the the date values; this gives us a separate query we can test, and we can assign column names to the expressions. Here, I use the alias dr (date range) for the inline views:

Oracle

FROM mytable t
JOIN ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'Q'),3) AS bd_next_qtr
            , ADD_MONTHS(TRUNC(SYSDATE,'Q'),-3) AS bd_prev_qtr
         FROM DUAL
     ) dr
  ON t.mydate >= dr.bd_prev_qtr
 AND t.mydate <  dr.bd_next_qtr

---

MySQL

FROM mytable t
JOIN ( SELECT MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-2 QUARTER AS bd_next_qtr
            , MAKEDATE(YEAR(NOW()),1) + INTERVAL QUARTER(NOW())-0 QUARTER AS bd_prev_qtr
     ) dr
  ON t.mydate >= dr.bd_prev_qtr
 AND t.mydate <  dr.bd_next_qtr

Answer 2

I would get the start date of you 1st quarter (min_date) and the end date of your 2nd quarter (max_date) to get a proper range, then do the following:

SELECT model_id,model_name,Effective_date_from,effective_date_to 
FROM model 
WHERE active='YES'
 AND (Effective_date_from BETWEEN min_date AND max_date OR effective_date_to BETWEEN min_date AND max_date)
;

This should give you the models that were active at some point during those 2 quarters.

Answer 3

Did you try:

SELECT
    * 
FROM
    yourTable
WHERE 
    yourDate  >= DATE_FORMAT( CURRENT_DATE - INTERVAL 3 MONTH, '%Y/%m/01' ) 
AND
    yourDate  < DATE_FORMAT( CURRENT_DATE- INTERVAL 1 MONTH, '%Y/%m/01');