Getting data from Current Month backwards as Past Due and Future

Fabian
Fabian Member
in Magic ETL

I am trying this code but still cannot get data from previous month from last year 2024. All I got in "Past Due" is data from current month (January) and in "Future" I get all the data from future month plus from last year

CASE WHEN MONTH(`SSD`)<=MONTH(CURRENT_DATE())AND YEAR(`SSD`)<=YEAR(CURRENT_DATE()) THEN 'Past Due' else 'Future' END

Comments

  • ggenovese
    ggenovese Coach
    edited January 30

    The issue you're running into is that month('2024-12-01') is greater than month('2025-01-01')

    if you break up your case statement that should fix it for you: 

    CASE
WHEN YEAR(`SSD`)< YEAR(CURRENT_DATE())
    
THEN 'Past Due'
MONTH(`SSD`)<= MONTH(CURRENT_DATE())
    
AND YEAR(`SSD`)= YEAR(CURRENT_DATE())
    
THEN 'Past Due'
ELSE 'Future'
END

  • ColemenWilson
    ColemenWilson Coach
    edited January 30

    I don't see anything wrong in your beast mode code other than perhaps a space between CURRENT_DATE()) & AND here: MONTH(`SSD`)<=MONTH(CURRENT_DATE())AND . It would be helpful to see a sample of your data.

    If I solved your problem, please select "yes" above

  • Fabian
    Fabian Member

    Thank you both

Categories