[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
?
332 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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 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…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

650 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