Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# real quick problem with a join (urgent)

Posted on 2010-09-03
Medium Priority
379 Views
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?
``````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
``````
0
Question by:dbaSQL
[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
• 29
• 13
• 6
• +1

LVL 17

Author Comment

ID: 33598974
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.Quantity [FQuantity],o.Quantity [OQuantity],o.Price,om.OMarking,
o.OrderNo,o.EventTime [OrderTime],t.EventTime [ExecutionTime],om.FPosition,c.EventTime [CancelTime]

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
0

LVL 41

Expert Comment

ID: 33599146
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
``````
0

LVL 41

Expert Comment

ID: 33599157
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
``````
0

LVL 59

Expert Comment

ID: 33599161
you should be ordering by

ORDER BY o.OrderID, o.EventTime

0

LVL 59

Expert Comment

ID: 33599171
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
....
0

LVL 17

Author Comment

ID: 33599254
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?

``````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
``````
0

LVL 17

Author Comment

ID: 33599282
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
0

LVL 41

Expert Comment

ID: 33599306
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
``````
0

LVL 17

Author Comment

ID: 33599315
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.
0

LVL 41

Expert Comment

ID: 33599380
So yeah, please my second suggestion a shot http:#a33599306
0

LVL 59

Expert Comment

ID: 33599455

``````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
``````
0

LVL 17

Author Comment

ID: 33599483
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.
0

LVL 17

Author Comment

ID: 33599587
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
0

LVL 17

Author Comment

ID: 33599620
do either of you have any ideas?  i apologize, but this is becoming really very pressing.
0

LVL 41

Expert Comment

ID: 33599663
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
``````
0

LVL 17

Author Comment

ID: 33599683
bless you. trying it now,  back shortly
0

LVL 59

Expert Comment

ID: 33599719
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
``````
0

LVL 50

Expert Comment

ID: 33599757
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
``````
0

LVL 17

Author Comment

ID: 33600044
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?
0

LVL 17

Author Comment

ID: 33600157
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?
0

LVL 59

Expert Comment

ID: 33600324
Lowfatspread, is your query different than the one I posted @ 33599719 (I could not locate the difference)

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)
``````
0

LVL 59

Expert Comment

ID: 33600350
I got the difference

LEFT JOIN vs. LEFT OUTER JOIN

but they are same, OUTER is optional value...
0

LVL 17

Author Comment

ID: 33600400
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
0

LVL 41

Expert Comment

ID: 33600620
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
0

LVL 17

Author Comment

ID: 33600684
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
``````
0

LVL 17

Author Comment

ID: 33600703
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.

0

LVL 17

Author Comment

ID: 33600715
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.
0

LVL 17

Author Comment

ID: 33600809
and remember, we don't care about the A messages (don't want them)
0

LVL 41

Expert Comment

ID: 33601166
``````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
``````
0

LVL 17

Author Comment

ID: 33601404
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.
``````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
``````
0

LVL 41

Accepted Solution

ID: 33601429
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:

``````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
``````
0

LVL 17

Author Comment

ID: 33601443
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
0

LVL 17

Author Comment

ID: 33601716
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?
``````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
``````
0

LVL 17

Author Comment

ID: 33601722
Still can't get the o message up top the list, though.

ORDER BY o.OrderNo, o.messagetype DESC
0

LVL 41

Expert Comment

ID: 33603340
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'
0

LVL 17

Author Comment

ID: 33607068
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
0

LVL 17

Author Comment

ID: 33608705
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
0

LVL 17

Author Comment

ID: 33608730
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
0

LVL 17

Author Comment

ID: 33608872
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo
AND c.messagetype = COALESCE('X','C')

yeah?
0

LVL 17

Author Comment

ID: 33608996
well, it seems appropriate for the conditional join,  but my record counts dropped by more than half.  still looking.
0

LVL 41

Expert Comment

ID: 33613179
>>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<<

``````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
``````
0

LVL 17

Author Comment

ID: 33614049
If there is an X and a C, how does that guarantee I will use the X, ralmada?
0

LVL 41

Expert Comment

ID: 33614458
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.
0

LVL 17

Author Comment

ID: 33621357
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?
0

LVL 17

Author Comment

ID: 33621490
i got it:

OrderTime = (SELECT EventTime FROM dbo.ordertable WHERE OrderNo = o.OrderNo AND messagtype = 'O'),

back in a bit with status
0

LVL 41

Expert Comment

ID: 33621533
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,
0

LVL 41

Expert Comment

ID: 33621548
ok, I guess you figured it out. I would still use the select max(eventtime) rather than the plain select eventtime

0

LVL 17

Author Comment

ID: 33621564
0

LVL 17

Author Comment

ID: 33624135
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.... :-)
0

## Featured Post

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
###### Suggested Courses
Course of the Month8 days, 10 hours left to enroll