Solved

MySql Statement to generate sub-resutls.

Posted on 2011-03-18
8
332 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Query concatenation 12 46
MYSQL simple update statement 3 34
2 Access tables, count verbiage used 6 20
Help with SQL Query 23 39
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now