CODEWITHSUNDEEP
sqlhivehiveql
trougc
trougc

Reputation: 405

How to get last one month's data from a table based on current month and year?

I am facing some problem with the hive code. My FROM TABLE is partitioned based on month, year and day. I came up with the following code to get the data I need. The logic is something like if the current mth is 01 then change the month to 12 and the year to yr - 1 else change month to mth - 1 and keep the year as is.

set hivevar:yr=2019;
set hivevar:mth=03;
set hivevar:dy=29;

SELECT * from 
FROM table
WHERE 
month = case when cast('${mth}' as int) = 01 then 12 else cast((cast('${mth}' as int) - 1) as string) end
AND year = case when cast('${mth}' as int) = 01 then cast((cast('${yr}' as int) - 1) as string) else '${yr}' end;

It is not working, my select * is coming empty. Please help.

desc table table

Upvotes: 0

Views: 409

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

From what i understand, you are trying to get data from the previous month given a date. If so, you can use inbuilt date functions to do it.

select *
from table 
where concat_ws('-',year,month,day) >= add_months(date_add(concat_ws('-','${yr}','${mth}','${dy}'),1-'${dy}'), -1) 
and concat_ws('-',year,month,day) < date_add(concat_ws('-','${yr}','${mth}','${dy}'),1-'${dy}')

The solution assumes year, month and day are of the format yyyy, MM and dd. If not, adjust them as needed

Also, you should consider storing date as a column even though you have it partitioned by year,month and day.

Upvotes: 1

Related Questions