Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Course Of The Month
7 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
real quick problem with a join (urgent)
Posted on 2010-09-03
three tables: ordertable, canceltable, markingtable
The ordertable contains different message types: O, A, T
The canceltable contains different message types: X, C
Where 'O' is an order message, 'T' is a trade message. ('A' is an ack).
The only guaranteed message in all cases is the 'O' message. (it i the origination of the order)
The unique value in all three tables is OrderNo.
I am joining the ordertable to itself on OrderNo, in order to present the details of both the O messages and the T messages. That is then joined to the marking table, to retrieve varied details about the marking of each message.
For the most part, it seems correct, but it has come to my attention that my resulting data is missing records. I'm looking at one orderno now, for example, and it exists in the ordertable 13 times -- one O, one A, and eleven T's (eleven trades).
My output, however, is only including four of those T messages.
This is very urgent. Does anybody see it?
The ordertable contains different message types: O, A, T
The canceltable contains different message types: X, C
Where 'O' is an order message, 'T' is a trade message. ('A' is an ack).
The only guaranteed message in all cases is the 'O' message. (it i the origination of the order)
The unique value in all three tables is OrderNo.
I am joining the ordertable to itself on OrderNo, in order to present the details of both the O messages and the T messages. That is then joined to the marking table, to retrieve varied details about the marking of each message.
For the most part, it seems correct, but it has come to my attention that my resulting data is missing records. I'm looking at one orderno now, for example, and it exists in the ordertable 13 times -- one O, one A, and eleven T's (eleven trades).
My output, however, is only including four of those T messages.
This is very urgent. Does anybody see it?
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM
dbo.ordertable o LEFT JOIN dbo.ordertable t ON o.OrderNo = t.OrderNo AND o.messageType = 'O' AND t.messageType = 'T'
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND o.messageType = 'O' AND c.messageType = 'X'
LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
WHERE (o.EventTime BETWEEN '1/1/10' AND '5/1/10')
AND (o.Symbol IN (........big list of symbols........))
GROUP BY o.Exchange, o.Symbol, o.Type, t.Quantity, o.Quantity, o.Price, om.OMarking, o.OrderNo, o.EventTime,t.EventTime,om.FPosition,c.EventTime
ORDER BY o.EventTime
[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
-
29
13
6- +1
49 Comments
Active 4 days ago
The requirement is simple; I need to return in ONE line, the details for each order message. The ordertable is joined to itself to return O and T details, then it is joined to canceltable and marking table.
If canceltime is NULL, it just means it wasn't canceled. IF ExecutionTime is NULL, it means it wasn't traded, or has no messageType = T, etc.
EXAMPLE:
o.Exchange,o.Symbol,t.Quan
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPositi
The 111 orderno is in ordertable three times, three message types: O, A, T.
It is not in the cancel table (wasn't canceled).
It is in the marking table once. (always one record in marking table)
So, joining ordertable to itself on OrderNo, only worried about the O and the T, I want this:
NSDQ, GDX, 400, 400, 49.05, L, 111, 2010-01-10 09:00:11.4170000, 2010-01-10 09:00:11.4970000, 1320, NULL
If canceltime is NULL, it just means it wasn't canceled. IF ExecutionTime is NULL, it means it wasn't traded, or has no messageType = T, etc.
EXAMPLE:
o.Exchange,o.Symbol,t.Quan
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPositi
The 111 orderno is in ordertable three times, three message types: O, A, T.
It is not in the cancel table (wasn't canceled).
It is in the marking table once. (always one record in marking table)
So, joining ordertable to itself on OrderNo, only worried about the O and the T, I want this:
NSDQ, GDX, 400, 400, 49.05, L, 111, 2010-01-10 09:00:11.4170000, 2010-01-10 09:00:11.4970000, 1320, NULL
just one thing. try your WHERE clause like below:
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM
( select * from dbo.ordertable
WHERE (o.EventTime BETWEEN '1/1/10' AND '5/1/10')
AND (o.Symbol IN (........big list of symbols........))
) o
LEFT JOIN dbo.ordertable t ON o.OrderNo = t.OrderNo AND o.messageType = 'O' AND t.messageType = 'T'
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND o.messageType = 'O' AND c.messageType = 'X'
LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
GROUP BY o.Exchange, o.Symbol, o.Type, t.Quantity, o.Quantity, o.Price, om.OMarking, o.OrderNo, o.EventTime,t.EventTime,om.FPosition,c.EventTime
ORDER BY o.EventTime
drop the alias there though:
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM
( select * from dbo.ordertable
WHERE (EventTime BETWEEN '1/1/10' AND '5/1/10')
AND (Symbol IN (........big list of symbols........))
) o
LEFT JOIN dbo.ordertable t ON o.OrderNo = t.OrderNo AND o.messageType = 'O' AND t.messageType = 'T'
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND o.messageType = 'O' AND c.messageType = 'X'
LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
GROUP BY o.Exchange, o.Symbol, o.Type, t.Quantity, o.Quantity, o.Price, om.OMarking, o.OrderNo, o.EventTime,t.EventTime,om.FPosition,c.EventTime
ORDER BY o.EventTime
Active 1 day ago
or do this
FROM (select * from ordertable where o.messageType = 'O') o left join (select * from ordertable where o.messageType = 'T') t on o.orrderid=t.orderid
....
FROM (select * from ordertable where o.messageType = 'O') o left join (select * from ordertable where o.messageType = 'T') t on o.orrderid=t.orderid
....
Active 4 days ago
thank you, thank you, thank you, ralmada.
it still returns me three records, though. I need a single record, per orderno. The top record is correct, it has all the values from the table joined on itself, and the others. I don't want the two with the NULL values.
do you know what i mean?
it still returns me three records, though. I need a single record, per orderno. The top record is correct, it has all the values from the table joined on itself, and the others. I don't want the two with the NULL values.
do you know what i mean?
NSDQ GDX 400 400 49.05000000 L 835YO3K3C42M 2010-05-10 09:00:11.4170000 2010-05-10 09:00:11.4970000 1320 NULL
NSDQ GDX NULL 400 49.05000000 L 835YO3K3C42M 2010-05-10 09:00:11.4200000 NULL 1320 NULL
NSDQ GDX NULL 400 49.05000000 L 835YO3K3C42M 2010-05-10 09:00:11.4970000 NULL 1320 NULL
Active 4 days ago
ralmada, i just added this line into the inner select, to test one of the supsect orderno's:
AND OrderNo = '835YO3K3C42M'
that's how i came up w/the result above
AND OrderNo = '835YO3K3C42M'
that's how i came up w/the result above
like this?
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM
( select * from dbo.ordertable
WHERE (EventTime BETWEEN '1/1/10' AND '5/1/10')
AND (Symbol IN (........big list of symbols........))
) o
LEFT JOIN dbo.ordertable t ON o.OrderNo = t.OrderNo AND o.messageType = 'O' AND t.messageType = 'T'
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND o.messageType = 'O' AND c.messageType = 'X'
LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
WHERE t.OrderNo is not null
GROUP BY o.Exchange, o.Symbol, o.Type, t.Quantity, o.Quantity, o.Price, om.OMarking, o.OrderNo, o.EventTime,t.EventTime,om.FPosition,c.EventTime
ORDER BY o.EventTime
Active 4 days ago
hainkurt, i can't do that, as the join must reference all three tables. often times, there is no data in the canceltable, but more often, there is. ralmada, that top line in the result i ran on one orderno is perfect. i just want to dump the other two records that are returned.
So yeah, please my second suggestion a shot http:#a33599306
Active 1 day ago
can you please try this
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM
(select * from dbo.ordertable where EventTime BETWEEN '1/1/10' AND '5/1/10' and Symbol IN (........big list of symbols........) and messageType = 'O') o
LEFT JOIN (select * from dbo.ordertable where messageType = 'T') t ON o.OrderNo = t.OrderNo
LEFT JOIN (select * from dbo.canceltable c where messageType = 'X') c ON o.OrderNo = c.OrderNo
LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
GROUP BY o.Exchange, o.Symbol, o.Type, t.Quantity, o.Quantity, o.Price, om.OMarking, o.OrderNo, o.EventTime,t.EventTime,om.FPosition,c.EventTime
ORDER BY o.EventTime
Active 4 days ago
almost, ralmada. it works perfectly for the one orderno i referenced above: '835YO3K3C42M'
only one record is returned, with all of correct data.
that orderno has an O, A and T in the ordertable, no records in the canceltable, and one record in the marking table.
if i do it on a message that has an O and A in the ordertable, and X and C in the canceltable, and one record in the markingtable, it gives back nothing. i believe that is of course because there is no T in the ordertable. but, i still need to return one line -- it will simply have NULL executiontime and NULL FQuantity, because it was not actuall traded.
That's why i was doing the LEFT JOIN on the ordertable. Everything has an O, I need to derive the t.Quantity, t.EventTime, c.CancelTime and the om values, IF that OrderNo is also in ordertable as a T message, and IF that orderno is in the canceltable with an X, and IF that orderno is in the marking table.
only one record is returned, with all of correct data.
that orderno has an O, A and T in the ordertable, no records in the canceltable, and one record in the marking table.
if i do it on a message that has an O and A in the ordertable, and X and C in the canceltable, and one record in the markingtable, it gives back nothing. i believe that is of course because there is no T in the ordertable. but, i still need to return one line -- it will simply have NULL executiontime and NULL FQuantity, because it was not actuall traded.
That's why i was doing the LEFT JOIN on the ordertable. Everything has an O, I need to derive the t.Quantity, t.EventTime, c.CancelTime and the om values, IF that OrderNo is also in ordertable as a T message, and IF that orderno is in the canceltable with an X, and IF that orderno is in the marking table.
Active 4 days ago
very similar, hainkurt, but it also returns two records, when i expect one.
i'm trying it on an orderno right now that has two records in ordertable: O, A
two records in canceletable: X, C
one record in marking table.
i would expect one value returned with NULL executiontime and NULL fquantity, because there is no T message.
I am getting two records back, however. and the ordertime for the second one is coming from the A messagetype.
I only need O and T from ordertable, and X from canceltable.
NSDQ SSO NULL 2900 41.17000000 L FWBH3BDP1RSN 2010-05-10 07:11:53.7870000 NULL 158111 2010-05-10 07:12:01.4810000
NSDQ SSO NULL 2900 41.17000000 L FWBH3BDP1RSN 2010-05-10 07:11:53.8030000 NULL 158111 2010-05-10 07:12:01.4810000
i'm trying it on an orderno right now that has two records in ordertable: O, A
two records in canceletable: X, C
one record in marking table.
i would expect one value returned with NULL executiontime and NULL fquantity, because there is no T message.
I am getting two records back, however. and the ordertime for the second one is coming from the A messagetype.
I only need O and T from ordertable, and X from canceltable.
NSDQ SSO NULL 2900 41.17000000 L FWBH3BDP1RSN 2010-05-10 07:11:53.7870000 NULL 158111 2010-05-10 07:12:01.4810000
NSDQ SSO NULL 2900 41.17000000 L FWBH3BDP1RSN 2010-05-10 07:11:53.8030000 NULL 158111 2010-05-10 07:12:01.4810000
Active 4 days ago
do either of you have any ideas? i apologize, but this is becoming really very pressing.
I think you need something like
select * from (
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime], row_number() over (partition by o.OrderNo order by o.OrderNo) rn
FROM
(select * from dbo.ordertable where EventTime BETWEEN '1/1/10' AND '5/1/10' and Symbol IN (........big list of symbols........) and messageType = 'O') o
LEFT JOIN (select * from dbo.ordertable where messageType = 'T') t ON o.OrderNo = t.OrderNo
LEFT JOIN (select * from dbo.canceltable c where messageType = 'X') c ON o.OrderNo = c.OrderNo
LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
GROUP BY o.Exchange, o.Symbol, o.Type, t.Quantity, o.Quantity, o.Price, om.OMarking, o.OrderNo, o.EventTime,t.EventTime,om.FPosition,c.EventTime
) a
where rn = 1
ORDER BY EventTime
Active 1 day ago
if you have only one rec with 'O' in order table no need to group
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM
(select * from dbo.ordertable where EventTime BETWEEN '1/1/10' AND '5/1/10' and Symbol IN (........big list of symbols........) and messageType = 'O') o
LEFT JOIN (select * from dbo.ordertable where messageType = 'T') t ON o.OrderNo = t.OrderNo
LEFT JOIN (select * from dbo.canceltable c where messageType = 'X') c ON o.OrderNo = c.OrderNo
LEFT JOIN dbo.markingtable om ON o.OrderNo = om.OrderNo
ORDER BY o.OrderID
and this query should return one record as long as ordertable has one 'O' max for a given orderid
try
SELECT
o.Exchange,o.Symbol,t.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.maxevent [ExecutionTime],om.FPosition,c.EventTime [CancelTime]
FROM (select * from dbo.ordertable
WHERE messagetype='O'
and EventTime BETWEEN '1/1/10' AND '5/1/10'
AND Symbol IN (........big list of symbols........))
) as o
LEFT outer JOIN
(select orderno,sum(quantity) as quantity,max(eventtime) as maxevent
from dbo.ordertable
where messagetype='T'
group by orderno
) as t ON o.OrderNo = t.OrderNo
LEFT outer JOIN
(select * from dbo.canceltable
where messagetype='X'
) as c ON o.OrderNo = c.OrderNo
LEFT outer JOIN dbo.markingtable om
ON o.OrderNo = om.OrderNo
ORDER BY o.EventTime
Active 4 days ago
well, i thought we were there, ralmada. until i saw that order messages that had multiple T's, were not accounted for in the output. for example,
-I've got one in ordertable 13 times. (one O, one A, eleven T's)
-it is not in cancel table (wasn't canceled)
-it is in the marking table.
Your new suggested construct returns this:
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1230000 -6205 NULL
But, the fQuantity is the quantity from only one of the T messages.
I have asked up top, and I am told that we need to treat each T separately because the executiontime impacts the firm's position at that time. I was in error by attempting to return the T messages collectively, as opposed to each in their own returned values, per orderno.
So I need to return one line for the order – which is the composite of the O message and the X message from the JOIN between ordertable and candeltable, on orderno. this record woud have NULL executiontime and NULL fquantity, and it should be sequenced by each of the T messages, in lines of their own, each explicitly returned. maybe there will be only one, maybe there will be more than one, but each T message needs to come back on its own. IF there are more than one, they all have the same OrderTime, but each has its own ExecutionTime and FQuantity.
I'm sorry, ralmada. i more than appreciate your assistance with this. can it be done?
-I've got one in ordertable 13 times. (one O, one A, eleven T's)
-it is not in cancel table (wasn't canceled)
-it is in the marking table.
Your new suggested construct returns this:
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1230000 -6205 NULL
But, the fQuantity is the quantity from only one of the T messages.
I have asked up top, and I am told that we need to treat each T separately because the executiontime impacts the firm's position at that time. I was in error by attempting to return the T messages collectively, as opposed to each in their own returned values, per orderno.
So I need to return one line for the order – which is the composite of the O message and the X message from the JOIN between ordertable and candeltable, on orderno. this record woud have NULL executiontime and NULL fquantity, and it should be sequenced by each of the T messages, in lines of their own, each explicitly returned. maybe there will be only one, maybe there will be more than one, but each T message needs to come back on its own. IF there are more than one, they all have the same OrderTime, but each has its own ExecutionTime and FQuantity.
I'm sorry, ralmada. i more than appreciate your assistance with this. can it be done?
Active 4 days ago
ralmada, lowfat and hainkurt, thank you very much for all of your time. i apologize for the mistaken post. each of your suggestions is very close to, or perfect, for the requested single line result. i'm trying to get the T messages out after that line, though.
first line is just the composite between ordertable and canceltable
second or multiple lines thereafter are the T messages, if exists.
ExecutionTime and FQuantity are NULL in the first record, and included in each of the subsequent T messages, if exists.
what do you think?
first line is just the composite between ordertable and canceltable
second or multiple lines thereafter are the T messages, if exists.
ExecutionTime and FQuantity are NULL in the first record, and included in each of the subsequent T messages, if exists.
what do you think?
Active 1 day ago
Lowfatspread, is your query different than the one I posted @ 33599719 (I could not locate the difference)
you should really post some sample data
you should really post some sample data
OrderTable
1 O 1/1/2010
1 T 2/1/2010
1 T 3/1/2010
1 T 4/1/2010
CancelTable
1 X
MarketingTable
1 M
and query (should) return
1 O 1/1/2010 T 2/1/2010 X M
1 O 1/1/2010 T 3/1/2010 X M
1 O 1/1/2010 T 4/1/2010 X M
because we have 1 O, 3 T, 1 X, 1 M (1 * 3 * 1 * 1 = 3 records...)
so where is the problem here? and you keep saying you need one record! how you can get one record if you have multiple T's? do you want first T, last T or all T's (which you have now)
Active 1 day ago
I got the difference
LEFT JOIN vs. LEFT OUTER JOIN
but they are same, OUTER is optional value...
LEFT JOIN vs. LEFT OUTER JOIN
but they are same, OUTER is optional value...
Active 4 days ago
hainkurt, i clearly indicated in post 33600044 and 33600157, that i was in error by attemtpting a single line resultset. the T messages have to be returned separately.
the example you posted should return four lines -
- first line is the composite between ordertable and canceltable, but ExecutionTime and FQuantity are NULL.
- The subsequent three lines would be the T messages, ExecutionTime and FQuantity are populated from each of the T messages.
Again, I was in error. I still need to perform the same LEFT construct -- all messages have an O, only some have T's, and cancels, and marking messages. IF the orderno has multiple T messages, i need to return them each, distinctly
the example you posted should return four lines -
- first line is the composite between ordertable and canceltable, but ExecutionTime and FQuantity are NULL.
- The subsequent three lines would be the T messages, ExecutionTime and FQuantity are populated from each of the T messages.
Again, I was in error. I still need to perform the same LEFT construct -- all messages have an O, only some have T's, and cancels, and marking messages. IF the orderno has multiple T messages, i need to return them each, distinctly
It will be better if you can post some sample data and the expected result (what is the end result from the query) along with the rationale as to why it has to be like this
Active 4 days ago
hopefully this will post well
ordertable
messagetype,endpoint,symbol,quantity,price,orderno,eventtime
O BEX AMD 1900 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
A BEX AMD 1900 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 100 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 200 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 200 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 200 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 100 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 100 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 100 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 300 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 100 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 100 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
T BEX AMD 400 8.88000000 FWBL6J2Z23OX 2010-05-10 10:08:36.1230000
canceltable
eventtime, orderno, messagetype
this order was not canceled, it is not in the canceltable
markingtable
eventtime, orderno, omarking, fposition
2010-05-10 10:08:36.1230000,FWBL6J2Z23OX, S, -6205
result:
Exchange Symbol FQuantity OrderQuantity Price OMarking OrderNo OrderTime ExecutionTime FPosition CancelTime
BEX AMD NULL 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 NULL -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 200 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 200 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 200 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 300 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 400 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
Active 4 days ago
didn't post as well as i'd hoped. but the first chunk just lists the messages in the ordertable, you will see the O, A and elevent T's.
Second chunk is NULL because this order was not canceled. IF it had been, there would be an X in there.
third chunk is the marking table.
last chunk is the intended result. you see there are twelve lines. one for the O message and X, if exists, along with markingtable details. and then eleven for each of the T messages. This one is kind of extreme. typically you get 1 to 4 T messages, per OrderNo. sometimes, however, there are no T messages.
please let me know if it is unclear, ralmada
Second chunk is NULL because this order was not canceled. IF it had been, there would be an X in there.
third chunk is the marking table.
last chunk is the intended result. you see there are twelve lines. one for the O message and X, if exists, along with markingtable details. and then eleven for each of the T messages. This one is kind of extreme. typically you get 1 to 4 T messages, per OrderNo. sometimes, however, there are no T messages.
please let me know if it is unclear, ralmada
Active 4 days ago
also, you see the OrderQuantity = 1900. That is the quantity the original order was submitted as. If you sum the quantity of each of the T's, you get 1900.
What about like this then?
select
o.endpoint,
o.symbol,
case when o.messagetype = 'T' then o.quantity end as FQuantity,
(select sum(quantity) from ordertable where orderno = o.orderno and messagetime = 'O') as OrderQuantity,
o.Price,
m.omarking,
o.OrderNo,
o.OrderTime,
o.eventtime as ExecutionTime,
m.fposition,
c.eventime
from (
select * from ordertable o
where o.messagetype in ('O','T')
) o
left join markingtable m on o.orderno = m.orderno
left join canceltable c on o.orderno = c.orderno
order by o.orderno, o.messagetype desc
Active 4 days ago
Almost perfect, ralmada. All of the correct records are displayed, only the ordermessage that is supposed to be first, is last. and the executiontime is not NULL. it should be NULL for any record that is not a T message.
hopefully it will post well
i'm going to try it out with some other ordernos, that have different conditions. (those with cancels, those without T's)
i thought i'd order by quantity, to get the NULL fquantity up top, but it didn't work out.
hopefully it will post well
i'm going to try it out with some other ordernos, that have different conditions. (those with cancels, those without T's)
i thought i'd order by quantity, to get the NULL fquantity up top, but it didn't work out.
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 200 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 200 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 200 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 300 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 100 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD 400 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
BEX AMD NULL 1900 8.88000000 S FWBL6J2Z23OX 2010-05-10 10:08:36.1230000 2010-05-10 10:08:36.1240000 -6205 NULL
1) the order should be correct if you do as I've indicated in the above query (sorting by messagetype DESC)
order by o.orderno, o.messagetype desc
2) to correct the execution time issue, see the below:
order by o.orderno, o.messagetype desc
2) to correct the execution time issue, see the below:
select
o.endpoint,
o.symbol,
case when o.messagetype = 'T' then o.quantity end as FQuantity,
(select sum(quantity) from ordertable where orderno = o.orderno and messagetime = 'O') as OrderQuantity,
o.Price,
m.omarking,
o.OrderNo,
o.OrderTime,
case when o.messagetype = 'T' then o.eventtime end as ExecutionTime,
m.fposition,
c.eventime
from (
select * from ordertable o
where o.messagetype in ('O','T')
) o
left join markingtable m on o.orderno = m.orderno
left join canceltable c on o.orderno = c.orderno
order by o.orderno, o.messagetype desc
Active 4 days ago
i haven't tested the other message types yet, and i have to run a quick errand. i'll be back soon, and I wiil sort as you've suggested (and correct my executiontime). back soon, ralmada. thank you for all of the assistance
Active 4 days ago
Though the o message is still ordering at the bottom, I think we are much closer.
I have checked the following types of messages:
835YO3K3C42M This orderno has an O, A and one T in ordertable, no cancels, and one marking.
Returns two records, the first is the composite between ordertable.O, cancels (none exist), and marking, where executiontime and fquantity are NULL.
The second is the T message, and executiontime and fquantity are not null.
835YO3K3C42O This orderno has an O and A in ordertable, an X in canceltable, no marking record.
Returns one record, where executiontime and fquantity are NULL (no T's), and canceltime is the eventime from canceltable.
FWBH2POLKCXT This orderno has an O, an A and four T messages in ordertable, no cancels and one marking.
Returns as five records, where execuitontime and fquantity are NULL in the first, and populated in the remaining four (the T messages).
FWBL6J2Z23OX This orderno has an O, an A and eleven T messages in ordertable, no cancels and one marking.
Returns twelve records, where executiontime and fquantity are NULL in the first, and populated for the remaining eleven (the T messages).
alll very good.
FWBH3BDP1RSN This orderno has an O and A in ordertable, (no T's), an X and C in canceltable, and one marking.
Returns two records -- it should onlly return one.
The return is pasted below, it is returning a record for both the X and the C in the canceltable.
In the ordertable we only care about O and T. We should ignore the A's.
In the canceltable we only care about the X. We should ignore the C's.
I changed my canceltable LEFT JOIN to this:
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND c.MsgType <> 'C'
It works, it only returns one record, for the ordertable.O, canceltable.X, and markingtable.
ralmada, what do you think? there are millions and millions of order messages. of those that I checked above, I think that's a pretty decent variety. they're all good (with my changed LEFT JOIN).
what do you think?
I have checked the following types of messages:
835YO3K3C42M This orderno has an O, A and one T in ordertable, no cancels, and one marking.
Returns two records, the first is the composite between ordertable.O, cancels (none exist), and marking, where executiontime and fquantity are NULL.
The second is the T message, and executiontime and fquantity are not null.
835YO3K3C42O This orderno has an O and A in ordertable, an X in canceltable, no marking record.
Returns one record, where executiontime and fquantity are NULL (no T's), and canceltime is the eventime from canceltable.
FWBH2POLKCXT This orderno has an O, an A and four T messages in ordertable, no cancels and one marking.
Returns as five records, where execuitontime and fquantity are NULL in the first, and populated in the remaining four (the T messages).
FWBL6J2Z23OX This orderno has an O, an A and eleven T messages in ordertable, no cancels and one marking.
Returns twelve records, where executiontime and fquantity are NULL in the first, and populated for the remaining eleven (the T messages).
alll very good.
FWBH3BDP1RSN This orderno has an O and A in ordertable, (no T's), an X and C in canceltable, and one marking.
Returns two records -- it should onlly return one.
The return is pasted below, it is returning a record for both the X and the C in the canceltable.
In the ordertable we only care about O and T. We should ignore the A's.
In the canceltable we only care about the X. We should ignore the C's.
I changed my canceltable LEFT JOIN to this:
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo AND c.MsgType <> 'C'
It works, it only returns one record, for the ordertable.O, canceltable.X, and markingtable.
ralmada, what do you think? there are millions and millions of order messages. of those that I checked above, I think that's a pretty decent variety. they're all good (with my changed LEFT JOIN).
what do you think?
NSDQ SSO NULL 2900 41.1700 L FWBH3BDP1RSN 2010-05-10 07:11:53.7870000 NULL 158111 2010-05-10 07:12:01.4810000
NSDQ SSO NULL 2900 41.1700 L FWBH3BDP1RSN 2010-05-10 07:11:53.7870000 NULL 158111 2010-05-10 07:12:01.4970000
Active 4 days ago
Still can't get the o message up top the list, though.
ORDER BY o.OrderNo, o.messagetype DESC
ORDER BY o.OrderNo, o.messagetype DESC
sorry long night I guess, it should be ASCENDING (since O comes first!)
ORDER BY o.OrderNo, o.messagetype
Regarding your previous question, sounds fair enough filtering by msgtype <> 'C'
ORDER BY o.OrderNo, o.messagetype
Regarding your previous question, sounds fair enough filtering by msgtype <> 'C'
Active 4 days ago
no apologies necessary, ralmada. i think it is good. i have put a great deal of data together, and am analyzing it. again, i think it is good. but i can't seal the deal until my associates review come tuesday am. I will let you know. again, thank you for all of your assistance with this. very much appreciated.
i'll be back soon
i'll be back soon
Active 4 days ago
just keeps getting better and better. :-(
the canceltable has X and C's -- the X is preferred in the left join from ordertable to canceltable. BUT, if there is only a C, I need to join to it, instead.
If there is an X and a C, give me the X
If there is only an X, give me the X
If there is only a C, give me the C
Can I CASE that LEFT JOIN, on the messagetype, ralmada?
left join canceltable c on o.orderno = c.orderno
the canceltable has X and C's -- the X is preferred in the left join from ordertable to canceltable. BUT, if there is only a C, I need to join to it, instead.
If there is an X and a C, give me the X
If there is only an X, give me the X
If there is only a C, give me the C
Can I CASE that LEFT JOIN, on the messagetype, ralmada?
left join canceltable c on o.orderno = c.orderno
Active 4 days ago
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo
AND (CASE WHEN c.messagetype = 'X' THEN 1
ELSE (CASE WHEN c.messagetype = 'C' THEN 1 ELSE 0 END) END)=1
still not quite right. I need another condition in there -
when c.messagetype in ('x','c') then X
still working it
AND (CASE WHEN c.messagetype = 'X' THEN 1
ELSE (CASE WHEN c.messagetype = 'C' THEN 1 ELSE 0 END) END)=1
still not quite right. I need another condition in there -
when c.messagetype in ('x','c') then X
still working it
Active 4 days ago
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo
AND c.messagetype = COALESCE('X','C')
yeah?
AND c.messagetype = COALESCE('X','C')
yeah?
Active 4 days ago
well, it seems appropriate for the conditional join, but my record counts dropped by more than half. still looking.
>>If there is an X and a C, give me the X
If there is only an X, give me the X
If there is only a C, give me the C<<
what about this?
If there is only an X, give me the X
If there is only a C, give me the C<<
what about this?
LEFT JOIN (
select t1.* from dbo.canceltable t1
where t1.MsgType = (select max(MsgType) from dbo.cancelTable where OrderNo = t1.OrderNo)
) c ON o.OrderNo = c.OrderNo
because the max(MsgType) will always give X if there's an X and C. IF there's a C only it will bring C, and finally if there's only an X, it will bring an X.
Active 4 days ago
ok ralmada. 99.9% there. seriously sweet. thank you.
o.OrderTime,
case when o.messagetype = 'T' then o.eventtime end as ExecutionTime,
I need the o.OrderTime to always be the EventTime of the O message.
I cased it, but the OrderTime is coming back NULL for the T messages. It should be the same, the Eventtime for the O message, for all related T messages.
Make sense?
o.OrderTime,
case when o.messagetype = 'T' then o.eventtime end as ExecutionTime,
I need the o.OrderTime to always be the EventTime of the O message.
I cased it, but the OrderTime is coming back NULL for the T messages. It should be the same, the Eventtime for the O message, for all related T messages.
Make sense?
Active 4 days ago
i got it:
OrderTime = (SELECT EventTime FROM dbo.ordertable WHERE OrderNo = o.OrderNo AND messagtype = 'O'),
back in a bit with status
OrderTime = (SELECT EventTime FROM dbo.ordertable WHERE OrderNo = o.OrderNo AND messagtype = 'O'),
back in a bit with status
change the
case when o.messagetype = 'T' then o.eventtime end as ExecutionTime
to
(select max(eventtime) from ordertable where orderno = o.orderno and messagetime = 'O') as Executiontime,
case when o.messagetype = 'T' then o.eventtime end as ExecutionTime
to
(select max(eventtime) from ordertable where orderno = o.orderno and messagetime = 'O') as Executiontime,
ok, I guess you figured it out. I would still use the select max(eventtime) rather than the plain select eventtime
Active 4 days ago
I think we got it, ralmada. There is an off chance that something else will seem amiss in the output.... I researching orderno's and answering questions non-stop since I ran the last version of this construct. Right now, however, it looks good.
I want to thank you each for all of the input. extra thanks to you, ralmada. very pointed and direct logic, easy to follow, and extremely helpful
i say that in retrospect, of course.... :-)
I want to thank you each for all of the input. extra thanks to you, ralmada. very pointed and direct logic, easy to follow, and extremely helpful
i say that in retrospect, of course.... :-)
Featured Post
The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience.
Defining the name:
When I talk to people they say different names on this subject stuff l…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 15 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.