Query returning empty months

Solomon Yakobson

You need to define 5 years. Do you mean from January 1, 2018 or from March 12, 2017? I'll assume latter:

with t as (
            select  0 comision,
                    add_months(trunc(sysdate,'yy'),(1-level) * 12) efectiva
              from  dual
              connect by level <= 6
           union all
            select  comision,
                    trunc(efectiva,'yy')
              from  risk_facturas
              where fefectiva >= add_months(trunc(sysdate),-60)
                and efectiva < trunc(sysdate) + 1
                and organizacion_id = :APP_GET_ORGANIZACION_ID
          )
select  null          NullValue,
        sum(comision) value,
        efectiva      label
  from  t
  group by efectiva
  order by efectiva
/

SY.

Solomon Yakobson

And if you what from January 1, 2018:

with t as (
            select  0 comision,
                    add_months(trunc(sysdate,'yy'),(1-level) * 12) efectiva
              from  dual
              connect by level <= 5
           union all
            select  comision,
                    trunc(efectiva,'yy')
              from  risk_facturas
              where fefectiva >= add_months(trunc(sysdate,'yy'),-48)
                and efectiva < trunc(sysdate) + 1
                and organizacion_id = :APP_GET_ORGANIZACION_ID
          )
select  null          NullValue,
        sum(comision) value,
        efectiva      label
  from  t
  group by efectiva
  order by efectiva
/

SY.

Javier P

Hi SY,

I mean five years back from the end of the current year.

Thanks a lot for your help!

Solomon Yakobson

Actually, basd on

efectiva < trunc(sysdate) + 1

in your original query year ago would be March 13, not March 12, so you'd need to consired adding 1 to

fefectiva >= add_months(trunc(sysdate,'yy'),-60)

or

fefectiva >= add_months(trunc(sysdate,'yy'),-48)

SY.

Javier P

if is today would be:

From 01-JAN-2018

To: 31-DEC-2022

Those 5 years

Thanks

Javier P

Yes,

The difference is that in the other query I was grouping by month and taking 12 months back.

Here am grouping by years taking 5 years back.

Thanks

Solomon Yakobson

@Javier P: I mean five years back from the end of the current year.

with t as (
      select 0 comision,
          add_months(trunc(sysdate,'yy'),(1-level) * 12) efectiva
       from dual
       connect by level <= 5
      union all
      select comision,
          trunc(efectiva,'yy')
       from risk_facturas
       where fefectiva >= add_months(trunc(sysdate,'yy'),-48)
        and efectiva < add_months(trunc(sysdate,'yy'),12)
        and organizacion_id = :APP_GET_ORGANIZACION_ID
     )
select null     NullValue,
    sum(comision) value,
    efectiva   label
 from t
 group by efectiva
 order by efectiva
/

SY.

Javier P

Thanks a lot SY.