• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 334
  • Last Modified:

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:

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)

Open in new window


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

Open in new window


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)

Open in new window


But what would be the proper syntax for that in one SQL statement?
0
cashonly
Asked:
cashonly
1 Solution
 
SharathData EngineerCommented:
Can you try if this is taking care of excluding the weekends?
UPDATE full_daily_data fdd1 
       INNER JOIN full_daily_data fdd2 
               ON fdd2.Trade_Date = DATE_SUB(fdd1.Trade_Date, INTERVAL CASE DAYOFWEEK(fdd1.Trade_Date) WHEN 2 THEN 3 ELSE 1 END DAY)
   SET fdd1.Prior_Open = fdd2.Day_Open, 
       fdd1.Prior_Close = fdd2.Day_Close 

Open in new window

0
 
DOSLoverCommented:
We can use trade-date from valid_trading_days to compare against fdd2 as follows:
==========================================================
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 =
       (select max(v2.trade_date) from Valid_Trading_Days v2 where v2.trade_date < fdd1.Trade_Date)

=====================================================================
0
 
PortletPaulCommented:
this is a duplicate question really, I believe the function proposed there deals with this.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now