How to convert SQL Query to HiveSQL and get Min Date?

Question

How to convert SQL Query to HiveSQL and get Min Date instead of using datepart as following:

%sql 
-- To clear table if it already exists
DROP TABLE IF EXISTS bar;

-- Create temp table syntax
CREATE TEMP VIEW  bar AS

--// Start date containing information about year and quarter
SELECT 
min(cast (datepart(year, startdate)||datepart(quarter, startdate) as bigint)) as st_dte, 
max(cast (datepart(year, enddate)||datepart (quarter, enddate) as bigint)) as end_dte, a_id, carr as bar_code, 

case when wac < 100 
then 'Dom Flg bar' 
else 'Int Flg bar' end as bar_flag, 

case when a_id in (343, 455, 123, 656, 645) 
then 1 
else 0 
end as lcc_bar 

from oair_cardecode 
GROUP BY a_id, bar_code, bar_flag, lcc_bar;

--this code returns error in the databricks.

Sample data and desired results would really help. – Gordon Linoff Jan 25 '20 at 22:59 — Gordon Linoff, Jan 25 '20 at 22:59

score 1 · Answer 1 · answered Jan 26 '20 at 18:33

For Hive >= 1.3.0 use quarter(date) function, for Hive < 1.3.0 use ceil(month(date) / 3.0) as quarter

select 
      min(cast(concat(year(startdate),quarter(startdate)) as bigint)) as st_dte, 
      max(cast(concat(year(enddate),quarter(enddate)) as bigint))     as end_dte, 
      a_id, 
      carr as bar_code, 
      case when wac < 100 then 'Dom Flg bar' else 'Int Flg bar' end as bar_flag, 
      case when a_id in (343, 455, 123, 656, 645) then 1 else 0 end as lcc_bar 
from oair_cardecode 
group by a_id, carr, 
      case when wac < 100 then 'Dom Flg bar' else 'Int Flg bar' end, 
      case when a_id in (343, 455, 123, 656, 645) then 1 else 0 end;

How to convert SQL Query to HiveSQL and get Min Date?

1 Answers1