CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Geek
Geek

Reputation: 3329

oracle query to report data only for the 1st or 2nd half of the year

I have a report which should display enrollment data only within 2 date ranges Jan-June or July-dec depending on current date. Scenarios:

Current query reports everything past 6 months with his query.

select * from enrollement where enrollement_dt > add_months(sysdate - 6);

Is there any function available in oracle to do the same or how do i get the logic in a single statement?

Any help with this is highly appreciated.

Upvotes: 1

Views: 886

Answers (3)

psaraj12
psaraj12

Reputation: 5072

You can use the below to get the start date and end date for enrollment

        WITH data
         AS (SELECT TRUNC(SYSDATE) curr_date from dual
             ),
         d2
         AS (SELECT curr_date,
                    To_date('0107'
                            ||( Extract (year FROM curr_date) - 1 ), 'ddmmyyyy')
                       start_first_half,
                    To_date('3112'
                            ||( Extract (year FROM curr_date) - 1 ), 'ddmmyyyy')
                       end_first_half,
                    To_date('0101'
                            ||Extract (year FROM curr_date), 'ddmmyyyy')
                       start_second_half,
                    To_date('3006'
                            ||Extract (year FROM curr_date), 'ddmmyyyy')
                       end_second_half
             FROM   data)
    SELECT curr_date,
           CASE
             WHEN To_char(curr_date, 'MM') >= To_char(start_first_half, 'MM')
                  AND To_char(curr_date, 'MM') <= To_char(end_first_half, 'MM') THEN
             start_second_half
             ELSE start_first_half
           END start_date1,
           CASE
             WHEN To_char(curr_date, 'MM') >= To_char(start_first_half, 'MM')
                  AND To_char(curr_date, 'MM') <= To_char(end_first_half, 'MM') THEN
             end_second_half
             ELSE end_first_half
           END end_date1
    FROM   d2

You can use it in your query like below

  Select * from enrollment_table a, (WITH data
     AS (SELECT TRUNC(SYSDATE) curr_date from dual
         ),
     d2
     AS (SELECT curr_date,
                To_date('0107'
                        ||( Extract (year FROM curr_date) - 1 ), 'ddmmyyyy')
                   start_first_half,
                To_date('3112'
                        ||( Extract (year FROM curr_date) - 1 ), 'ddmmyyyy')
                   end_first_half,
                To_date('0101'
                        ||Extract (year FROM curr_date), 'ddmmyyyy')
                   start_second_half,
                To_date('3006'
                        ||Extract (year FROM curr_date), 'ddmmyyyy')
                   end_second_half
         FROM   data)
SELECT curr_date,
       CASE
         WHEN To_char(curr_date, 'MM') >= To_char(start_first_half, 'MM')
              AND To_char(curr_date, 'MM') <= To_char(end_first_half, 'MM') THEN
         start_second_half
         ELSE start_first_half
       END start_date1,
       CASE
         WHEN To_char(curr_date, 'MM') >= To_char(start_first_half, 'MM')
              AND To_char(curr_date, 'MM') <= To_char(end_first_half, 'MM') THEN
         end_second_half
         ELSE end_first_half
       END end_date1
FROM   d2 ) b
where a.enrollment_date >=b.start_date1
and a.enrollment_date <=b.end_date1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Basically you want to truncate to the half-year. But Oracle doesn't support this.

One method counts half-years and compares them. You want the previous half year from the current date. That would be:

select (extract(year from sysdate) * 2 + floor(extract(month from sysdate) - 1) / 6) - 1
from dual

You can use this same formula:

where (extract(year from enrollement_dt) * 2 + floor(extract(month from enrollement_dt) - 1) / 6) - 1 =
       extract(year from sysdate) * 2 + floor(extract(month from sysdate) - 1) / 6) - 1
      )

from dual;

Unfortunately that can't use an index on the column. So, we can revisit this. You can get the first day of the current half using some date arithmetic:

select trunc(sysdate, 'Q') - mod(floor((extract(month from sysdate) - 1) / 3), 2) * interval '3' month
from dual

That just needs to be plugged into a where clause:

where enrollement_dt >= trunc(sysdate, 'Q') - mod(floor((extract(month from sysdate) - 1) / 3), 2) * interval '3' month - interval '6' month and
      enrollement_dt < trunc(sysdate, 'Q') - mod(floor((extract(month from sysdate) - 1) / 3), 2) * interval '3' month

Voila! An expression that can even use an index.

Upvotes: 1

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

You may try below query - 

select *
from enrollement
WHERE TO_CHAR(enrollement_dt, 'MMYYYY') >= CASE WHEN TO_CHAR(SYSDATE, 'mm') <= '06'
                                                          THEN TO_DATE('07' || EXTRACT(YEAR FROM SYSDATE) - 1, 'MMYYYY') 
                                                ELSE THEN TO_DATE('01' || EXTRACT(YEAR FROM SYSDATE), 'MMYYYY')
                                           END
  AND TO_CHAR(enrollement_dt, 'MMYYYY') <= CASE WHEN TO_CHAR(SYSDATE, 'mm') <= '06'
                                                          THEN TO_DATE('12' || EXTRACT(YEAR FROM SYSDATE) - 1, 'MMYYYY') 
                                                ELSE THEN TO_DATE('06' || EXTRACT(YEAR FROM SYSDATE), 'MMYYYY')
                                           END

Upvotes: 1

Related Questions