Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Select Where date is last day of each month

Posted on 2011-09-23
13
Medium Priority
?
670 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 60

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 60

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 60

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 60

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 60

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 60

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 2000 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 60

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

877 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