Support Questions
Find answers, ask questions, and share your expertise

Create new table with records between two dates within date range in HIVE

New Contributor
I am looking for discharge within 30 days of admission. The first part of the code runs where I use case statements and create the two variables exp and exp1 , but the second when I try to look for 30 days within admission date doesn't work. How do I make this run in HIVE?

I added an example of what I have and what I would expect to get in the new table

CREATE TABLE  new AS 
        SELECT *, 
        case when status in ('28' ,'44' ,'13' ) then 1 else 0 end as exp,
         case when status ='33' then 1 else 0 end as exp1 
            from old
         WHERE DATE_FORMAT (clinic_admit_dt, 'yyyyMM') >=  '201608' AND DATE_FORMAT(clinic_discharge_dt, 'yyyyMM') <= '201610' , 30)
);sample.PNG
1 REPLY 1

Contributor

HI....You can use datediff function like this :

WHERE datediff(clinic_discharge_dt,clinic_admit_dt) <= 30

Hope this helps. If the comment helps you to find a solution or move forward, please accept it as a solution for other community members.