1

I am trying to grab weekly (as in Mon-Sun, specified by the mode=1 parameter to YEARWEEK) values of a stock from a MySQL table I have with daily low, high, open and close values.

For instance, the following query correctly gives me the highs and lows for the weeks between Jan 1st 2012 and March 1st 2012:

SELECT date, MIN(low), MAX(high) 
FROM daily_stock_values WHERE stock_id = SOMESTOCK 
AND date BETWEEN '2012-01-01' AND '2012-03-01' GROUP BY YEARWEEK(date, 1)

This correctly outputs:

+------------+-----------+-----------+
| date       | max(high) | min(low)  |
+-----------+-----------+-----------+
| 2012-01-01 | ......... | ......... |
| 2012-01-02 | ......... | ......... |
| 2012-01-09 | ......... | ......... |
| 2012-01-16 | ......... | ......... |
| 2012-01-23 | ......... | ......... |
| 2012-01-30 | ......... | ......... |
| 2012-02-06 | ......... | ......... |
| 2012-02-13 | ......... | ......... |
| 2012-02-20 | ......... | ......... |
| 2012-02-27 | ......... | ......... |
+------------+-----------+-----------+

As you can see, the dates all correctly specify (besides the first one which is the lower range of the results) a Monday, which is the first day of the Mon-Sun week.

However, when I try to introduce a first and last as well (as described here):

SELECT date, MIN(low), MAX(high),
SUBSTRING_INDEX(GROUP_CONCAT(CAST(open AS CHAR) ORDER BY date), ',', 1) AS open, 
SUBSTRING_INDEX(GROUP_CONCAT(CAST(close AS CHAR) ORDER BY date DESC), ',', 1) AS close
FROM daily_stock_values WHERE stock_id = SOMESTOCK 
AND date BETWEEN '2012-01-01' AND '2012-03-01' GROUP BY YEARWEEK(date, 1)

This unfortunately messes up my GROUP BY and causes the dates to NOT be aligned to Mondays anymore:

+------------+-----------+-----------+-----------+-----------+
| date       | max(high) | min(low)  | open      | close     |
+------------+-----------+-----------+-----------+-----------+
| 2012-01-01 | ......... | ......... | ......... | ......... |
| 2012-01-08 | ......... | ......... | ......... | ......... |
| 2012-01-15 | ......... | ......... | ......... | ......... |
| 2012-01-22 | ......... | ......... | ......... | ......... |
| 2012-01-29 | ......... | ......... | ......... | ......... |
| 2012-02-05 | ......... | ......... | ......... | ......... |
| 2012-02-12 | ......... | ......... | ......... | ......... |
| 2012-02-19 | ......... | ......... | ......... | ......... |
| 2012-02-26 | ......... | ......... | ......... | ......... |
| 2012-02-27 | ......... | ......... | ......... | ......... |
+------------+-----------+-----------+-----------+-----------+

What is going on here? Thanks!

Community
  • 1
  • 1
user1094786
  • 6,402
  • 7
  • 29
  • 42

2 Answers2

3

The best solution that I was able to come up with is:
SELECT YEARWEEK(date, 1) AS ignore, MIN(date) as date

This works quite well!

user1094786
  • 6,402
  • 7
  • 29
  • 42
0

Actually, your first query is already "messed up". You are selecting dates and grouping by weeks. Think it this way: what date do you expect to be displayed if you had two dates in the same week?

Your options are to select and group by YEARWEEK(date, 1) or to select and group by date.

Btw, you should consider providing more information on the tables and data they contain and your expected results.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • thanks for the reply! the date I need selected is the first date in the observation period, which is either Monday, or if the first day given isn't a Monday, its that day (just like the first result, unlike the second one). in this case, what exactly should I be selected as far as dates are concerned? – user1094786 Apr 10 '12 at 03:00