CREATE TABLE IF NOT EXISTS `OPT_FTSEMIB` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`SDATE` date NOT NULL,
`SPOT` float NOT NULL,
`EXPIRY` char(5) NOT NULL,
`TYPE` char(1) NOT NULL,
`STRIKE` int(11) NOT NULL,
`SETTLEMENT` int(11) NOT NULL,
PRIMARY KEY (`ID`)
)
Yes. Can be done.
I think.
It depends a bit on what situations can occur, and whether or not you
want to prevent rows from occuring multiple times.
>So I would basically like to SELECT SDATE, SPOT, EXPIRY and those 4
>columns (which are rows actually) FROM table WHERE EXPIRY="DEC15" AND
>TYPE="C"
My questions:
a) Do you _always_ have groups of exactly four rows for each
EXPIRY=value AND TYPE=value?
b) What would you want to have returned if rows were present like:
(982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(983, '2012-08-21', 15330, 'DEC15', 'C', 10000, 14),
(984, '2012-08-21', 15330, 'DEC15', 'C', 10000, 15),
OR
(982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(983, '2012-08-21', 15331, 'DEC15', 'C', 11000, 13),
(984, '2012-08-21', 15332, 'DEC15', 'C', 12000, 13),
OR EVEN
(982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(983, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
(984, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
?
Depending on your answers, a solution could go into the direction of a
query with several subqueries (one for each additional STRIKE field
value):
SELECT t1.sdate, t1.spot, t1.expiry,
t1.settlement AS c1,
(SELECT t2.settlement AS s2 FROM `OPT_FTSEMIB` t2 WHERE t2.expiry='DEC15' AND t2.type='C' AND t2.id>t1.id AND t2.sdate=t1.sdate
ORDER BY t2.id LIMIT 1) AS c2,
[...etc...two more times]
FROM `OPT_FTSEMIB` t1
WHERE t1.expiry='DEC15' AND t1.type='C'
ORDER BY <whatever order you like to have>
[Neither tested nor optimized. Just a first thought.
And be prepared to receive questions about the normalization of your data model from several denizens here in the group]
> [Neither tested nor optimized. Just a first thought.
> And be prepared to receive questions about the normalization of
> your data model from several denizens here in the group]
> b) What would you want to have returned if rows were present like:
> (982, '2012-08-21', 15330, 'DEC15', 'C', 10000, 13),
> (983, '2012-08-21', 15330, 'DEC15', 'C', 10000, 14),
> (984, '2012-08-21', 15330, 'DEC15', 'C', 10000, 15),
STRIKE(10000) can have only one value for that EXPIRY(DEC15) in that very day
> Yes. Can be done.
> I think.
> It depends a bit on what situations can occur, and whether or not you
> want to prevent rows from occuring multiple times.
Maybe I should explain my request a little better, take a look a this:
SELECT SDATE, SPOT, SETTLEMENT AS C10000
FROM OPT_FTSEMIB
WHERE EXPIRY = "DEC15"
AND TYPE = "C"
AND STRIKE =10000
>>Yes. Can be done.
>>I think.
>>It depends a bit on what situations can occur, and whether or not
>>you want to prevent rows from occuring multiple times.
>.......SDATE...SPOT...C10000...C11000
>2012-08-20...14971...4473........3893
>2012-08-21...15330...4605........4019
>Is that possible?
Yes, it's what I described previously.
Have you tried my example with the subqueries?
>>Yes, it's what I described previously.
>>Have you tried my example with the subqueries?
>Yep, it does not work properly..
Try bringing your car to the workshop, claiming that "it doesn't drive
properly". How, would you guess, are the chances that the mechanic will
stare at you, waiting for further clues?
> Try bringing your car to the workshop, claiming that "it doesn't drive
> properly". How, would you guess, are the chances that the mechanic will
> stare at you, waiting for further clues?
I think maybe putting each single result into an associative array could do that. Something like:
SELECT SETTLEMENT FROM OPT_FTSEMIB WHERE STRIKE=10000 AND TYPE="C" AND EXPIRY="DEC15"
SELECT SETTLEMENT FROM OPT_FTSEMIB WHERE STRIKE=11000 AND TYPE="C" AND EXPIRY="DEC15"
SELECT SETTLEMENT FROM OPT_FTSEMIB WHERE STRIKE=12000 AND TYPE="C" AND EXPIRY="DEC15"
..ecc..(actually the range is from 10000 to 40000)
On Fri, 26 Oct 2012 10:59:29 +0200, Larry wrote:
> I think maybe putting each single result into an associative array could
> do that. Something like:
....
> $array->{"2012-08-20"}->{SPOT}=SPOT
Question - are you trying to achieve this solely in sql, or are you calling the sql from another language, and if so, what language?
It seems to me that what you want to do is:
1) select distinct type, strike from the existing table
2) build a temporary table with sdate, spot, expiry, "type+strike" columns for each distinct pairing of type and strike found
3) load the sdate, spot and expiry cols of the temporary table with the distinct sdate, spot and expiry groupings from the original table
4) populate each of the "type+strike" columns of the temporary table, using the relevant settlement values from the original table
5) pull your data from the temporary table
This is probably the point at which someone makes a comment about getting your table design right to start with, if no-one's already said it!
