CODEWITHSUNDEEP
sqljoinhivehql
Sotark
Sotark

Reputation: 196

Using data from one table to filter a second table without joining

I have a table of data that I need to pull data from based on the date it was updated. I need to grab the max date of the update column, then select my criteria, based on that day for the previous 7 days. I have accomplished that by creating a temp table which stores the update date.

drop table last_upd;
create temporary last_upd as 
select to_date(max(updt_ts)) as last_update from tableA
    where creat_dt > '2020-01-01';

I then want to actually pull my data from the table for only the date range I have specified. I am doing it this way, bc this data is not updated daily currently. So I need to report the day it was updated, and the contents for the previous 7 days. I am currently doing this by running the above query to determine the date, then I update the hardcoded date in my second query to reflect this range. I am trying to understand the best way to do this without having ot update my code every time.

drop table table_Data;
create temporary table table_data as 
select *
from TableA
where to_date(updt_ts) between date_add('2020-06-23',-7) and '2020-06-23';

I tried doing different things with joining the table but that doesn't work. I ultimately just need to know what the max date is then use that as my range.

Upvotes: 0

Views: 531

Answers (1)

Sayon M
Sayon M

Reputation: 146

You can try something like this:

select *
from tableA as a,
  (select to_date(max(updt_ts)) as last_update 
  from tableA
  where creat_dt > '2020-01-01') as b
where to_date(updt_ts) between date_add(b.last_update,-7) and b.last_update;

Upvotes: 1

Related Questions