cashonly
asked on
Need to replace a value in a SQL update statement based on looking for valid days in a table
I'm looking at a SQL statement where I need to access the prior day's data in a table that has a unique index by symbol + trade_date. I can do something like this:
but that won't take into account holidays and weekends.
So, if I make a single column VALID_TRADING_DAYS table that contains just the days that are valid for the trading data I have, how could I get that info out of the table and replace it into the "1" in the statement above?
Psuedocode-wise, this is how I envision it:
But what would be the proper syntax for that in one SQL statement?
update fdd1
set fdd1.Prior_Open = fdd2.Day_Open,
fdd1.Prior_Close = fdd2.Day_Close
from full_daily_data fdd1
inner join full_daily_data fdd2
on fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL 1 DAY)
but that won't take into account holidays and weekends.
So, if I make a single column VALID_TRADING_DAYS table that contains just the days that are valid for the trading data I have, how could I get that info out of the table and replace it into the "1" in the statement above?
Psuedocode-wise, this is how I envision it:
Days_Back = 1
Select Trade_Date as prior_trading_day from VALID_TRADING_DAYS where trade_date = DATE_SUB(fdd1.Trade_Date, INTERVAL Days_Back DAY)
while no rows returned
Days_Back = Days_Back +1
Select Trade_Date as prior_trading_day from VALID_TRADING_DAYS where trade_date = DATE_SUB(fdd1.Trade_Date, INTERVAL Days_Back DAY)
wend
Then I could have this SQL statement instead
update fdd1
set fdd1.Prior_Open = fdd2.Day_Open,
fdd1.Prior_Close = fdd2.Day_Close
from full_daily_data fdd1
inner join full_daily_data fdd2
on fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL Days_Back DAY)
But what would be the proper syntax for that in one SQL statement?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
this is a duplicate question really, I believe the function proposed there deals with this.
Open in new window