Solved

MySql Statement to generate sub-resutls.

Posted on 2011-03-18
8
338 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
Independent Software Vendors: 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!

 

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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