MySQL Select Where date is last day of each month

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!

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.

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!

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`.

Well, unfortunately the supplier of our data records the price on everyday using after hours prices if the market is closed.

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}
;

Select allOpen in new window

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. ;)

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.

Sorry - the asker posted that after I had started writing my response, mwvisa1! ;-)

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. :)

Wow, this is perfect!!! It worked like a charm. Thank you both soo much for all the help!

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

Kevin