LiveLabs & Workshops

select * from (your query) t
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = t.label

Thanks User_H3J7U
What do mean with your query?
The full select statement?

select * from (select
    null
  , sum(f.comision)         value
  , trunc(f.efectiva, 'MM') label
from
    risk_facturas f
where
    f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
and f.organizacion_id = :APP_GET_ORGANIZACION_ID
group by
    trunc(f.efectiva, 'MM')) t
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = t.label

Like this?
Thanks again!

Hi,
Am getting this error:
Thanks,

Replace "f.label" with:

trunc(f.efectiva, 'MM')

Label does not exist as a column on the risk_facturas table, it is just an alias and cannot be used in the same query except on the ORDER BY clause. Or you can use it on a higher level (parent) query, like this:

select * from ( select
    null AS NullValue
  , sum(f.comision)         AS value
  , trunc(f.efectiva, 'MM') AS label
from
    risk_facturas f
where
    f.efectiva >= add_months(trunc(sysdate), -13) and f.efectiva < trunc(sysdate) + 1
and f.organizacion_id = :APP_GET_ORGANIZACION_ID
group by
    trunc(f.efectiva, 'MM') ) oq
right join (select trunc(add_months(sysdate,1-level),'mm') d from dual connect by level<14) u
on u.d = oq.label

Hi Fernigrini,
Thanks for your help!
I will try it that way.
Thanks

with t as (
            select  0 comision,
                    trunc(add_months(sysdate,1-level),'mm') efectiva
              from  dual
              connect by level < 14
           union all
            select  comision,
                    trunc(efectiva,'MM')
              from  risk_facturas
              where fefectiva >= add_months(trunc(sysdate),-13)
                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.
-- correction: f. is removed

Hi SY
Thanks for the complete code and your help.
I appreciate it.

Hi Sy
Can you help me with the same query but returning the last 5 year and grouping by year?
I need the query group by year and returning all last 5 years no matter if it has any production
Return 0 If it does not have any production for that year.

Thanks a lot for your help!

XY question
In this case it is simple enough to use the APEX chart settings to handle the data densification automatically.

Hi fac586,
Yes, that is what I figured and am using the setting you mentioned to me but having other problems that I posted in the same thread.
Thanks always for you help!

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.

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.

Hi SY,
I mean five years back from the end of the current year.
Thanks a lot for your help!

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.

if is today would be:
From 01-JAN-2018
To: 31-DEC-2022
Those 5 years
Thanks

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

Thanks a lot SY.