?
Solved

Need to replace a value in a SQL update statement based on looking for valid days in a table

Posted on 2013-05-31
3
Medium Priority
?
326 Views
Last Modified: 2013-06-21
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
Comment
Question by:cashonly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 39211685
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
 
LVL 5

Accepted Solution

by:
DOSLover earned 2000 total points
ID: 39211883
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39212266
this is a duplicate question really, I believe the function proposed there deals with this.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question