Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 725
  • Last Modified:

MySQL Select Where date is last day of each month

Hello, I am trying to write a select query in MySQL where I pull data for the last day of every month between a specific range.

Lets say I have a data base called stockPrices with a date (yyyy-mm-dd), Ticker, Price. The data is daily. and I am trying to pull the price for the last trading day of every month between a specific rage of dates.

Thank you!
0
KOvitt
Asked:
KOvitt
  • 7
  • 3
  • 3
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hi.

Can each stock have a different last trading day? Or is finding the last trading day for one stock sufficient to get the last for all?

Either way, here is my suggestion. In a derived query/table, filter on your date range and GROUP BY YEAR(`DateColumn`), Month(`DateColumn`). Get the MAX(`DateColumn`) as your aggregate and you will have for each month within your range the last date in your data. Join that back to your data table where the `DateColumn` = MAX_DateColumn.

Something like this:

SELECT {columns you want}
FROM your_table a
JOIN (
   SELECT MAX(`DateColumn`) AS MAX_DateColumn
   FROM your_table
   GROUP BY YEAR(`DateColumn`), MONTH(`DateColumn`)
) b ON b.MAX_DateColumn = a.`DateColumn`

;

Hope that makes sense!
0
 
Kevin CrossChief Technology OfficerCommented:
Sorry, your filter would go here:

SELECT {columns you want}
FROM your_table a
JOIN (
   SELECT MAX(`DateColumn`) AS MAX_DateColumn
   FROM your_table
   WHERE `DateColumn` >= {startdate} AND `DateColumn` <= {enddate}
   GROUP BY YEAR(`DateColumn`), MONTH(`DateColumn`)
) b ON b.MAX_DateColumn = a.`DateColumn`
;

Ensuring to fix the >= and  <= appropriately for what you want to happen regarding inclusion of the date parameters.
0
 
KOvittAuthor Commented:
Theoretically find the date for one is sufficient. It needs to account for weekends and holidays as well though. I have a separate table of marketHolidays that I can check against if needed.

Thanks for the help!
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Kevin CrossChief Technology OfficerCommented:
If it is a market holiday, that date will not be in your data, right? Therefore, you do not have to worry about a table of dates in this instance as you looking for the last "actual" date of data. Regarding date, if you find you need it by stock, then just add that to the inner query like this:

   SELECT `Stock`, MAX(`DateColumn`) AS MAX_DateColumn
   FROM your_table
   WHERE `DateColumn` >= {startdate} AND `DateColumn` <= {enddate}
   GROUP BY YEAR(`DateColumn`), MONTH(`DateColumn`), `Stock`

Then your ON clause changes to include a.`Stock` = b.`Stock`.
0
 
KOvittAuthor Commented:
Well, unfortunately the supplier of our data records the price on everyday using after hours prices if the market is closed.
0
 
nemws1Database AdministratorCommented:
Why not just use the LAST_DAY function built into MySQL:
SELECT {columns you want}
FROM your_table
WHERE DATE(`DateColumn`) = LAST_DAY(`DateColumn`)
  AND `DateColumn` >= {startdate} AND `DateColumn` <= {enddate}
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Okay, then yes, you will need to exclude your marketHolidays and weekends. For the former, you can use a NOT EXISTS statement against that table. For the latter, you can use DAYNAME() or DAYOFWEEK() functions. Both filters would go in the WHERE clause of the inner query. The theory there is to do as much work up front, so the OUTER JOIN happens more efficiently as you have a very small set of data that will be matched versus joining larger data set only to filter on the outside.

Here are the manual references:
http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

Give it a try and post back if you have difficulties. This way will ensure you have a bit of understanding of what the code is doing, since it is getting increasingly complex and you have to maintain it. ;)
0
 
Kevin CrossChief Technology OfficerCommented:
LAST_DAY() returns the last calendar day of the month, it is not taking into account the data, especially given the new parameters of no weekends and holidays.
0
 
nemws1Database AdministratorCommented:
Sorry - the asker posted that after I had started writing my response, mwvisa1! ;-)
0
 
Kevin CrossChief Technology OfficerCommented:
No worries at all. Long time no see by the way. I hope all is well. The question started with "the last trading day of every month," which is why I went with the derived table approach. If you can think of a better way, please feel free. You usually have good insight in this arena. :)
0
 
nemws1Database AdministratorCommented:
Good to be back (although I wish it was under better circumstances - I got "downsized" out of my job, so I've got plenty of time on my hands right now!)

My gut wants me to replace LAST_DAY with a better version, something like LAST_TRADING_DAY (below).  I've got a table named 'bad_dates' that just contains work-week dates that are bad (the function takes care of weekends).

I don't like the fact that it's going to get called for every row though.  Would be nice to somehow run the function for just each month (a sub-select of some sort to trim that down?)
DELIMITER $$

DROP FUNCTION IF EXISTS LAST_TRADING_DAY$$

CREATE FUNCTION LAST_TRADING_DAY (input_date DATE)
    RETURNS DATE
BEGIN
    DECLARE is_bad INT;
    DECLARE work_date DATE;

    -- Start with last actual day of the month
    SET work_date = LAST_DAY(input_date);

    -- Move back from/ignore weekends - 5=Saturday, 6=Sunday
    IF (WEEKDAY(work_date) = 6) THEN
            SET work_date = DATE_SUB(work_date, INTERVAL 2 DAY);
    ELSEIF (WEEKDAY(work_date) = 5) THEN
            SET work_date = DATE_SUB(work_date, INTERVAL 1 DAY);
    END IF;

    -- Now work back (if needed) to find a good date
    FIND_LAST_TRADING_DAY: LOOP
        -- Check the bad_dates table for our current work_date
        SELECT COUNT(*)
            INTO is_bad
            FROM bad_dates
            WHERE bad_date = work_date;
        -- See if the date was bad or not
        IF (is_bad = 1) THEN
            -- Bad date, so back 1 day
            SET work_date = DATE_SUB(work_date, INTERVAL 1 DAY);
        ELSE
            LEAVE FIND_LAST_TRADING_DAY;
        END IF;
    END LOOP FIND_LAST_TRADING_DAY;

    RETURN work_date;
END $$

Open in new window


Once you've got the function, it should look pretty readable and straightforward (here's my actual SQL with my test tables/data I set up):
 
SELECT Price, pdate as DateColumn
FROM StockData
WHERE DATE(`pdate`) = LAST_TRADING_DAY(`pdate`) AND
    `pdate` >= '2011-01-20' AND
    `pdate` <= '2011-05-10'
ORDER BY `pdate`;

Open in new window

0
 
KOvittAuthor Commented:
Wow, this is perfect!!! It worked like a charm. Thank you both soo much for all the help!
0
 
Kevin CrossChief Technology OfficerCommented:
Good job, @nemws1!
@KOvitt: I am glad you got what you needed.
Best regards and happy coding,

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

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