CODEWITHSUNDEEP
sqlhive
Pratik Khadloya
Pratik Khadloya

Reputation: 12869

Hive get mimimum of two values for where clause

I am trying to write a hive query such that i need to use a date based where clause to filter on records.

WHERE date_key 
BETWEEN date_sub(current_date, 4) 
  AND MINIMUM(date_sub(current_date, 1), to_date("2018-10-14"))

Can we do something like MINIMUM of two values?

Upvotes: 0

Views: 161

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Don't bother. Just write:

WHERE date_key >= date_sub(current_date, 4) AND
      date_key <= date_sub(current_date, 1) AND
      date_key <= to_date('2018-10-14')

If something is less than the minimum of two values, then it is less than each of the values.

Upvotes: 1

George Joseph
George Joseph

Reputation: 5922

if you are using oracle then there is a keyword called "least" which will output the lesser of values. Eg: 

WHERE date_key 
BETWEEN date_sub(current_date, 4) 
  AND LEAST(date_sub(current_date, 1), to_date("2018-10-14"))

Otherwise you can handcraft the query to compare the two values as follows

WHERE date_key 
BETWEEN date_sub(current_date, 4) 
  AND (CASE WHEN date_sub(current_date, 1)< to_date("2018-10-14") THEN 
                 date_sub(current_date, 1)
            ELSE to_date("2018-10-14")
        END)

Upvotes: 2

Related Questions