SQL according to the fortnight of the current month in Oracle Apex

Question

My table name is SESSIONS. My fields for this table are: SESSION_DATE (type: date), SESSIONS.PRICE (type: Number), STATUS (type: Number).

I want my query to show different results depending on the current day. if the current day is in the first fortnight of the month, show different results than if it is in the second fortnight of the month.

This is my query:

select to_char(SESSION_DATE, 'YYYYMM') as CHARGE_MONTH,
  to_char(SESSION_DATE, 'Month YY') as CHARGE,
    sum(SESSIONS.PRICE) as PRICE
 from SESSIONS SESSIONS
 where SESSIONS.STATUS in (4,5)
 and SESSIONS.SESSION_DATE between add_months(trunc(CURRENT_TIMESTAMP,'mm'),-13) and last_day(add_months(trunc(CURRENT_TIMESTAMP,'mm'),-1))
 group by to_char(SESSION_DATE, 'YYYYMM'), to_char(SESSION_DATE, 'Month YY')
 order by to_char(SESSION_DATE, 'YYYYMM') Desc

If we are in the first fortnight of the current month then

SESSIONS.SESSION_DATE between add_months(trunc(CURRENT_TIMESTAMP,'mm'),-13) and last_day(add_months(trunc(CURRENT_TIMESTAMP,'mm'),-1))

else

SESSIONS.SESSION_DATE between add_months(trunc(CURRENT_TIMESTAMP,'mm'),-13) and last_day(add_months(trunc(CURRENT_TIMESTAMP,'mm'),0))

I tried something like that:

select to_char(SESSION_DATE, 'YYYYMM') as CHARGE_MONTH,
  to_char(SESSION_DATE, 'Month YY') as CHARGE,
    sum(SESSIONS.PRICE) as PRICE
 from SESSIONS SESSIONS
 where SESSIONS.STATUS in (4,5)
 and SESSION_DATE = case when to_char(current_timestamp, 'dd') < 15 then
between add_months(trunc(CURRENT_TIMESTAMP,'mm'),-13) and last_day(add_months(trunc(CURRENT_TIMESTAMP,'mm'),-1))
else
between add_months(trunc(CURRENT_TIMESTAMP,'mm'),-13) and last_day(add_months(trunc(CURRENT_TIMESTAMP,'mm'),0)) 
end
 group by to_char(SESSION_DATE, 'YYYYMM'), to_char(SESSION_DATE, 'Month YY')
 order by to_char(SESSION_DATE, 'YYYYMM') Desc;

but it didn't work:(ORA-00905: missing keyword)

I tried also this one but still nothing:

select to_char(SESSION_DATE, 'YYYYMM') as CHARGE_MONTH,
  to_char(SESSION_DATE, 'Month YY') as CHARGE,
    sum(SESSIONS.PRICE) as PRICE
 from SESSIONS SESSIONS
 where SESSIONS.STATUS in (4,5)
 and
    case when extract(day from current_timestamp) from dual < 15 then 
    (SESSION_DATE between add_months(trunc(CURRENT_TIMESTAMP,'mm'),-13) 
                  and last_day(add_months(trunc(CURRENT_TIMESTAMP,'mm'),-1)))
    else 
    (SESSION_DATE between add_months(trunc(CURRENT_TIMESTAMP,'mm'),-13) 
                  and last_day(add_months(trunc(CURRENT_TIMESTAMP,'mm'),0))) 
    end
 group by to_char(SESSION_DATE, 'YYYYMM'), to_char(SESSION_DATE, 'Month YY')
 order by to_char(SESSION_DATE, 'YYYYMM') Desc;

I am getting this error:

ORA-20999: Failed to parse SQL query! ORA-06550: line 8, column 12: ORA-00907: missing right parenthesis

Answer 1

You don't need a case expression in the where clause. You can just use regular boolean expressions:

select to_char(SESSION_DATE, 'YYYYMM') as CHARGE_MONTH,
       to_char(SESSION_DATE, 'Month YY') as CHARGE,
       sum(SESSIONS.PRICE) as PRICE
from SESSIONS SESSIONS
where SESSIONS.STATUS in (4, 5) and
      ( (extract(day from SESSIONS.SESSION_DATE) <= 14 and
         SESSIONS.SESSION_DATE between add_months(trunc(CURRENT_TIMESTAMP, 'mm'), -13) and last_day(add_months(trunc(CURRENT_TIMESTAMP, 'mm'), -1))
        ) or
        (extract(day from SESSIONS.SESSION_DATE) > 14) and
         SESSIONS.SESSION_DATE between add_months(trunc(CURRENT_TIMESTAMP, 'mm'), -13) and last_day(add_months(trunc(CURRENT_TIMESTAMP, 'mm'), 0))
        )
      )
group by to_char(SESSION_DATE, 'YYYYMM'), to_char(SESSION_DATE, 'Month YY')
order by to_char(SESSION_DATE, 'YYYYMM') Desc;