Solved

MySQL Select Where date is last day of each month

Posted on 2011-09-23
13
634 Views
Last Modified: 2012-06-27
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
Comment
Question by:KOvitt
  • 7
  • 3
  • 3
13 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589069
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589091
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
 

Author Comment

by:KOvitt
ID: 36589206
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589238
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
 

Author Comment

by:KOvitt
ID: 36589347
Well, unfortunately the supplier of our data records the price on everyday using after hours prices if the market is closed.
0
 
LVL 23

Expert Comment

by:nemws1
ID: 36589376
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589382
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589392
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
 
LVL 23

Expert Comment

by:nemws1
ID: 36589447
Sorry - the asker posted that after I had started writing my response, mwvisa1! ;-)
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36589515
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
 
LVL 23

Accepted Solution

by:
nemws1 earned 500 total points
ID: 36589736
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
 

Author Closing Comment

by:KOvitt
ID: 36589895
Wow, this is perfect!!! It worked like a charm. Thank you both soo much for all the help!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36590023
Good job, @nemws1!
@KOvitt: I am glad you got what you needed.
Best regards and happy coding,

Kevin
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 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