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
301 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
3 Comments
 
LVL 40

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 48

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now