Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySql Statement to generate sub-resutls.

Posted on 2011-03-18
8
Medium Priority
?
339 Views
Last Modified: 2012-05-11
I want to know if there is a way to generate a sub result set for each result return by the main query.  

Example:

Main result:
`StockSymbol`, `OpenDate`,`CloseDate`
AAPL              ,  1/1/10       , 1/4/10
Sub Result
`Date`,`Open`,`High`,`Low`,`Close`
1/1/10, 110,     112 ,  108,     109
1/2/10   109,   110,   103,   105
1/3/10   105,  109,  102,    108
1/4/10,   108,  109,  107,  107

So..For each Main Result returned there would be multiple sub results to go with that record.

In this example all the Open and Close data for all the Dates between the Open and Close of the AAPL position.

Thanks



0
Comment
Question by:_Carthmen_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 34

Expert Comment

by:Norie
ID: 35166105
You should be able to return that data but I'm not sure what you mean by a 'sub-result'.

It really depends on how your table(s) are set up.

What tables/fields do you currently have?

0
 

Author Comment

by:_Carthmen_
ID: 35180222
I think what i am looking for is a LEFT JOIN.

Orginally what i invisioned was a table out put that looked like.


`StockSymbol`, `OpenDate`,`CloseDate`
AAPL              ,  1/1/10       , 1/4/10
        `Date`,`Open`,`High`,`Low`,`Close`
         1/1/10, 110,     112 ,  108,     109
          1/2/10   109,   110,   103,   105
          1/3/10   105,  109,  102,    108
          1/4/10,   108,  109,  107,  107
AAPL               , 1/7/11,          2/4/11
             `Date`,`Open`,`High`,`Low`,`Close`
              More data  more data more data

But i think to get that I would have to use a LEFT JOIN or the link and then format the result in php or something.
0
 
LVL 34

Expert Comment

by:Norie
ID: 35180892
Yes, it probably is a LEFT JOIN  you need but without knowing more about the table structure it's hard to say exactly what the SQL would be.

I also think it's a yes to the formattin thing, you would need something else to produce what you want.

The results of the query would repeat the StockSymbol and Open/Close date for each record.

Could you post some sample data as it is in the tables?
0
Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

 

Author Comment

by:_Carthmen_
ID: 35181112
Here Is the table Structure I am using
 
SQL result

Host: localhost
Database: db
Generation Time: Mar 21, 2011 at 02:30 PM
Generated by: phpMyAdmin 2.11.8.1deb5+lenny6 / MySQL 5.0.51a-24+lenny4
SQL query: desc `OptionData` ; 
Rows: 21

Field	Type	Null	Key	Default	Extra
Symbol	varchar(8)	NO	 	NULL	 
Exchange	varchar(16)	NO	 	NULL	 
Date	varchar(10)	NO	 	NULL	 
Close	double	NO	 	NULL	 
OptionCode	varchar(256)	NO	 	NULL	 
Expiration	varchar(10)	NO	 	NULL	 
Strike	double	NO	 	NULL	 
Type	varchar(1)	NO	 	NULL	 
Ask	double	NO	 	NULL	 
Bid	double	NO	 	NULL	 
Mean	double	NO	 	NULL	 
IV	double	NO	 	NULL	 
Volume	bigint(20)	NO	 	NULL	 
OpenInterest	bigint(20)	NO	 	NULL	 
IVStockPrice	double	NO	 	NULL	 
Special	varchar(16)	NO	 	NULL	 
Delta	double	NO	 	NULL	 
Vega	double	NO	 	NULL	 
Gamma	double	NO	 	NULL	 
Theta	double	NO	 	NULL	 
Rho	double	NO	 	NULL

Open in new window


 
SQL result

Host: localhost
Database: db
Generation Time: Mar 21, 2011 at 02:31 PM
Generated by: phpMyAdmin 2.11.8.1deb5+lenny6 / MySQL 5.0.51a-24+lenny4
SQL query: desc `PriceData`; 
Rows: 10

Field	Type	Null	Key	Default	Extra
Symbol	varchar(8)	NO	 	NULL	 
Date	varchar(10)	NO	 	NULL	 
Open	double	NO	 	NULL	 
High	double	NO	 	NULL	 
Low	double	NO	 	NULL	 
Close	double	NO	 	NULL	 
Volume	bigint(20)	NO	 	NULL	 
AdjClose	double	NO	 	NULL	 
Change	double	NO	 	NULL	 
20dayStdDev	double	NO	 	NULL

Open in new window


This is the code I have come up with so far ( does not work )

 
SELECT
	`PriceData`.`Date`,
	`PriceData`.`Close`,
	`PriceData`.`Change`,
	`PriceData`.`Change` / `PriceData`.`20dayStdDev` AS `SpikeSize`,
	`Option`.`Date` AS `OptionDate`,
	`Option`.`Expiration`,
	`Option`.`Strike`,
	`Option`,`Delta`,
	`OptionData`.`OptionCode`,
	`OptionData`.`Close`,
	`OptionData`.`Date`

FROM
	`db`.`PriceData` AS `PriceData`,
	`db`.`OptionData` AS `Option`

LEFT JOIN
	`db`.`OptionData` AS `OptionData`

ON
	`OptionData`.`OptionCode` = `Option`.`OptionCode`

WHERE
	DATEDIFF(STR_TO_DATE(`Option`.`Expiration`, '%m/%d/%Y'),STR_TO_DATE(`Option`.`Date`,'%m/%d/%Y')) > 10
		AND
	DATEDIFF(STR_TO_DATE(`Option`.`Expiration`, '%m/%d/%Y'),STR_TO_DATE(`Option`.`Date`,'%m/%d/%Y')) < 45
		AND
	`PriceData`.`Close` - `OptionData`.`Strike` > -10 AND `PriceData`.`Close` - `OptionData`.`Strike` < 10
		AND
	`PriceData`.`Change` / `PriceData`.`20dayStdDev` > 2
		AND
	`OptionData`.`Delta` > .20 AND `OptionData`.`Delta` < .40

Open in new window


I want to get for Every Date that there is a StdDevSpike greater then 2 Find the OptionData for that date that has a delta between .2 and .4 with less then 45 days to expiration.

Then join with that all the data for those  option codes  each date till expiration

Sample Data:

 
SQL result

Host: localhost
Database: db
Generation Time: Mar 21, 2011 at 02:36 PM
Generated by: phpMyAdmin 2.11.8.1deb5+lenny6 / MySQL 5.0.51a-24+lenny4
SQL query: SELECT * FROM `PriceData` LIMIT 0, 30 ; 
Rows: 30

Symbol	Date	Open	High	Low	Close	Volume	AdjClose	Change	20dayStdDev
QCOM	12/16/91	16.38	18.58	16.38	17.61	143667200	0.49	0	0
QCOM	12/17/91	18.09	18.09	17.12	17.36	16176000	0.48	-0.25	0
QCOM	12/18/91	17.36	17.85	17.36	17.85	10899200	0.49	0.49	0
QCOM	12/19/91	17.85	17.85	17.12	17.12	7299200	0.47	-0.73	0
QCOM	12/20/91	17.12	17.85	17.12	17.61	5235200	0.49	0.49	0
QCOM	12/23/91	17.85	17.85	17.12	17.48	7894400	0.48	-0.13	0
QCOM	12/24/91	17.24	18.83	17.12	18.83	7129600	0.52	1.35	0

Open in new window



 
SQL result

Host: localhost
Database: db
Generation Time: Mar 21, 2011 at 02:37 PM
Generated by: phpMyAdmin 2.11.8.1deb5+lenny6 / MySQL 5.0.51a-24+lenny4
SQL query: SELECT * FROM `OptionData` LIMIT 0, 30 ; 
Rows: 30

Symbol	Exchange	Date	Close	OptionCode	Expiration	Strike	Type	Ask	Bid	Mean	IV	Volume	OpenInterest	IVStockPrice Price Used For IV Calculation	Special	Delta	Vega	Gamma	Theta	Rho
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619C00022500	06/19/10	22.5	C	13.2	12.45	12.825	0.51	0	0	35.07	*	0.99998	1e-05	2e-05	-0.00023	0.01048
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619P00022500	06/19/10	22.5	P	0.03	0	0	0.5243	0	313	35.07	*	-3e-05	1e-05	4e-05	-2e-05	-0
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619C00025000	06/19/10	25	C	10.3	10	10.15	0.51	0	0	35.07	*	0.99913	0.00022	0.00077	-0.00058	0.01163
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619P00025000	06/19/10	25	P	0.04	0.02	0.03	0.5243	44	348	35.07	*	-0.00115	0.00029	0.00096	-0.00045	-2e-05
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619C00030000	06/19/10	30	C	5.25	5.15	5.2	0.51	11	229	35.07	 	0.92993	0.01017	0.03481	-0.01552	0.01277
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619P00030000	06/19/10	30	P	0.15	0.13	0.14	0.5243	5	7647	35.07	 	-0.07521	0.01074	0.03575	-0.01653	-0.00129
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619C00031000	06/19/10	31	C	4.35	4.2	4.275	0.4839	0	0	35.07	 	0.89161	0.01408	0.0508	-0.0203	0.01257
QCOM	NASDAQ	06/01/10	35.07	QCOM  100619P00031000	06/19/10	31	P	0.23	0.2	0.215	0.4945	170	7	35.07	 	-0.11261	0.01468	0.05125	-0.02107	-0.00186

Open in new window

0
 

Accepted Solution

by:
_Carthmen_ earned 0 total points
ID: 35189221
Finally came up with this,seems to do close to what i need
SELECT
      `PriceData`.`Symbol`,
      STR_TO_DATE(`OptionData`.`Date`,'%m/%d/%Y') AS `Date`,
      `PriceData`.`Close`,
      `PriceData`.`Change`,
      `PriceData`.`20dayStdDev`,
      `PriceData`.`Change` / `PriceData`.`20dayStdDev` AS `SpikeSize`,
      STR_TO_DATE(`OptionData`.`Date`,'%m/%d/%Y') AS `OptionDate`,
      `OptionData`.`OptionCode`,
      DATEDIFF(STR_TO_DATE(`Option`.`Expiration`, '%m/%d/%Y'),STR_TO_DATE(`Option`.`Date`,'%m/%d/%Y')) AS 'DaysToExp' ,
      `Option`.`Strike`,
      `Option`.`Type`,
      `Option`.`Mean`,
      `Option`.`IV`,
      `Option`.`Delta`

FROM
      `db`.`PriceData` AS `PriceData`,
      `db`.`OptionData` AS `OptionData`
LEFT JOIN
      `db`.`OptionData` AS `Option`
ON
      `Option`.`OptionCode` = `OptionData`.`OptionCode`
            AND
      STR_TO_DATE(`Option`.`Date`,'%m/%d/%Y') > STR_TO_DATE(`OptionData`.`Date`, '%m/%d/%Y')
            AND
      STR_TO_DATE(`Option`.`Expiration`, '%m/%d/%Y') = STR_TO_DATE(`OptionData`.`Expiration`, '%m/%d/%Y')
WHERE
      DATEDIFF(STR_TO_DATE(`OptionData`.`Expiration`, '%m/%d/%Y'),STR_TO_DATE(`PriceData`.`Date`,'%m/%d/%Y')) < 45
            AND
      `PriceData`.`Close` - `OptionData`.`Strike` > -10 AND `PriceData`.`Close` - `OptionData`.`Strike` < 10
            AND
      `OptionData`.`Delta` > .20 AND `OptionData`.`Delta` < .40
            AND
      STR_TO_DATE(`OptionData`.`Date`, '%m/%d/%Y') = STR_TO_DATE(`PriceData`.`Date`, '%m/%d/%Y')
            AND
      `PriceData`.`Change` / `PriceData`.`20dayStdDev` > 2


            
0
 
LVL 34

Expert Comment

by:Norie
ID: 35189587
Glad you got it working and apoligise for not replying.

Was about to set something up last night from the data you posted about the tables but got distracted.
0
 

Author Comment

by:_Carthmen_
ID: 35198286
No problem, thanks for taking the time.
0
 

Author Closing Comment

by:_Carthmen_
ID: 35225280
Found this to the needed statement after working around with it a bit.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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