Solved

MySql Statement to generate sub-resutls.

Posted on 2011-03-18
8
334 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_
  • 5
  • 3
8 Comments
 
LVL 33

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 33

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 33

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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