cancel
Showing results for 
Search instead for 
Did you mean: 

Could not get last 3 month's data

Former Member
0 Kudos

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.

View Entire Topic
Former Member
0 Kudos

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
0 Kudos

Hi,

Any updates?

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

Regards,

Antony Jerald.

Former Member
0 Kudos

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
0 Kudos

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
0 Kudos

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.