sqlfunctiondatehivehue

How to separate a table from others using a column as a parameter


I have the following table with hundreds of rows: table

I'm trying to separate each line and send to the following tables: "access in the last 15 days", "access in the last 30 days" and "not accessed for more than 30 days".

According to the date present in the "tbdlf fsimage accesstime" column, make this separation and send it to the respective table.

I'm doing this through Hue File Browser Query Editors


Solution

  • you can calculate dates difference and use multi-insert to insert data to different tables based on condition:

    with src as (
    select t.* --list all columns which you need to insert
           --calculate category depending on difference in days
           case when datediff(current_date,to_date(accesstime))> 30 then 'not accessed for more than 30 days'
                when datediff(current_date,to_date(accesstime))> 15 then 'access in the last 30 days'
                when datediff(current_date,to_date(accesstime))<= 15 then 'access in the last 15 days'      
            end as category
      from tbdlf_fsimage t 
    )
    
    insert into table not_accessed_for_more_than_30_days
    select --list columns here
     from src 
     where category='not accessed for more than 30 days'
    
    insert into table access_in_the_last_30_days
    select --list columns here
     from src 
     where category='access in the last 30 days'
    
    insert into table access_in_the_last_15_days
    select --list columns here
     from src 
     where category='access in the last 15 days'
    ; --end of multi-insert