Forum Stats

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

Discussions

Query returning empty months

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,930 Red Diamond
    edited Mar 12, 2022 11:31AM

    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
    Solomon Yakobson Member Posts: 19,930 Red Diamond

    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
    Javier P Member Posts: 888 Silver Badge

    Hi SY,

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

    Thanks a lot for your help!

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

    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
    Javier P Member Posts: 888 Silver Badge

    if is today would be:

    From 01-JAN-2018

    To: 31-DEC-2022

    Those 5 years

    Thanks

  • Javier P
    Javier P Member Posts: 888 Silver Badge

    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
    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
  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Thanks a lot SY.