Could not get last 3 month's data

[Former Member]

Hi,

"

d.MONTH IN (EXTRACT_MONTH(CURRENT_DATE) - 2,EXTRACT_MONTH(CURRENT_DATE) - 1,EXTRACT_MONTH(CURRENT_DATE) + 0)

"

The above code now gives only current month's data.

We have 3 Months calculation view, where it shows 3 month's data(including the current month).  It was showing expected data since December month.  Since, it's the New year, it's not picking up last years' last 2 Months data and shows only current Month's data.

How could I correct my code here?  Kindly suggest.

Regards,

Antony Jerald.

[Former Member · Accepted solution]

Where do you have the function EXTRACT_MONTH from? It is not documented. Anyway, you can use ADD_MONTH(CURRENT_DATE, -3).

-- Micha

[Former Member · Accepted solution]

Hi,

Any updates?

Could you please tell me how to get all such available functions in Hana?

Regards,

Antony Jerald.

[Former Member · Accepted solution]

Hi,

I'm getting error while I try with the solution you've provided.  Kindly help me on the same.

Regards,

Antony Jerald.

[Former Member · Accepted solution]

Hi Anthony,

you'll have to change your  data. You cannot test the year and month separately if you want to go forward or backward in time, you have to use a date field and test it as a whole against the required threshold. So instead of

WHERE

    d.YEAR = EXTRACT_YEAR(CURRENT_DATE) + 0

    AND

    d.MONTH IN (EXTRACT_MONTH(CURRENT_DATE) - 2,EXTRACT_MONTH(CURRENT_DATE) - 1,EXTRACT_MONTH(CURRENT_DATE) + 0)

you would write something like

WHERE

d.date >= ADD_MONTHS(CURRENT_DATE, -3);

kind of simpler, even 🙂 If you don't want to or cannot change the schema of d, you can put the date together using the year and month, something like

TO_DATE(d.YEAR || '-' || d.MONTH, 'YYYY-MM') >= ADD_MONTHS(CURRENT_DATE, -3);

-- Micha

[Former Member · Accepted solution]

Hi,

Resolved my problem!!

(d.year,d.month) IN ((EXTRACT_YEAR(ADD_MONTHS(CURRENT_DATE,-2)),EXTRACT_MONTH(ADD_MONTHS(CURRENT_DATE,-2))),(EXTRACT_YEAR(ADD_MONTHS(CURRENT_DATE,-1)),EXTRACT_MONTH(ADD_MONTHS(CURRENT_DATE,-1))),(EXTRACT_YEAR(ADD_MONTHS(CURRENT_DATE,0)),EXTRACT_MONTH(ADD_MONTHS(CURRENT_DATE,0))))

I did wrote the above code hence, my problem resolved.

In your first reply, you've mentioned ADD_MONTH instead of ADD_MONTHS, which made me confuse there.

Regards,

Antony Jerald.