• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

MySql Statement to generate sub-resutls.

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
_Carthmen_
Asked:
_Carthmen_
  • 5
  • 3
1 Solution
 
NorieData ProcessorCommented:
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
 
_Carthmen_Author Commented:
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
 
NorieData ProcessorCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
_Carthmen_Author Commented:
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
 
_Carthmen_Author Commented:
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
 
NorieData ProcessorCommented:
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
 
_Carthmen_Author Commented:
No problem, thanks for taking the time.
0
 
_Carthmen_Author Commented:
Found this to the needed statement after working around with it a bit.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now