I am hoping to get some help in relation to the SQL query to pull the latest data till yesterday.
I have a main table called clickstream, this table has all the website activity and has a date column called businesseffectivedate, which provides the breakdown of each day's performance (visits, clicks, sales), etc.
I have create a query based on my requirement and I call this, TableA. My goal is to update the TableA every day based on the businesseffectivedate.
The data I want is, from August 1st, and for the end date I would like to get the latest data (i.e. yesterday Sep 23) I tried using the “current_date()” inside Where clause to get the latest data every day.
I was hoping that, I should get the latest data till yesterday based on the current_date() but I am still seeing the latest date is 22nd Sep (even when I refreshed the data today, ideally the date should be 23rd Sept instead.
1st Query (Tried and not working):
DROP TABLE IF EXISTS db.tableA;
CREATE TABLE db.tableA as
Select clicks, impressions, businesseffectivedate, sales from clickstream
where businesseffectivedate > '2022-07-31' and businesseffectivedate < current_date()
2nd Query (Tried and not working):
DROP TABLE IF EXISTS db.tableA;
CREATE TABLE db.tableA as
Select clicks, impressions, businesseffectivedate, sales from clickstream
where businesseffectivedate > '2022-07-31' and businesseffectivedate <= date_add(current_date(),-1)
3rd Query (Not tried yet):
DROP TABLE IF EXISTS db.tableA;
CREATE TABLE db.tableA as
Select clicks, impressions, businesseffectivedate, sales from clickstream
where businesseffectivedate > '2022-07-31' and businesseffectivedate < (select max(businesseffectivedate) from clickstream)
for clickstream data I ran this command, select max(businesseffectivedate) from clickstream // This gives me the answer of 2022-09-23 (correct)
I ran the same command for my tableA since the data in this table is coming from clickstream.
select max(businesseffectivedate) from tableA // I am seeing 2022-09-22 (in-correct)
Please advise how I can make this query automate (i.e. get the latest data everyday). FYI, I am SQL newbie and don’t have much experience with code, and not familiar with ROW/partition by etc.
Thanks!