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
317 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 500 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

688 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