Forum Stats

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

Discussions

Categories

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.

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • 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!

    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • 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

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,930 Red Diamond
    Answer ✓

    @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
    · Share on TwitterShare on Facebook
  • Javier P
    Javier P Member Posts: 888 Silver Badge

    Thanks a lot SY.

    · Share on TwitterShare on Facebook