CODEWITHSUNDEEP
sqlhiveqlapache-spark-sqldatabricksdatepart
zorost
zorost

Reputation: 11

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

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.

Upvotes: 1

Views: 89

Answers (1)

leftjoin
leftjoin

Reputation: 38290

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;

Upvotes: 1

Related Questions