Forum Stats

  • 3,873,344 Users
  • 2,266,537 Discussions
  • 7,911,514 Comments

Discussions

Query returning empty months

Javier P
Javier P Member Posts: 888 Silver Badge

Hi all,


I have this query that returns the production of the last 12 months.

This is the query:

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')

How can I get a line for every month no matter if it has any production or is just 0? Can this be done?

Thanks for the help!

Javier

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,930 Red Diamond

    @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
«1

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 1,062 Gold Trophy
    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
    
  • Javier P
    Javier P Member Posts: 888 Silver Badge

    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!

  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Hi,

    Am getting this error:

    Thanks,

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 4,094 Silver Crown

    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
    


    Javier P
  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Hi Fernigrini,

    Thanks for your help!

    I will try it that way.

    Thanks

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,930 Red Diamond
    edited Mar 10, 2022 8:39PM
    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

    Javier P
  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Hi SY

    Thanks for the complete code and your help.

    I appreciate it.

  • Javier P
    Javier P Member Posts: 888 Silver Badge

    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!

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,444 Red Diamond

    XY question

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


  • Javier P
    Javier P Member Posts: 888 Silver Badge

    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!