Solved

real quick problem with a join (urgent)

Posted on 2010-09-03
49
369 Views
Last Modified: 2012-05-10
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

Open in new window

0
Comment
Question by:dbaSQL
  • 29
  • 13
  • 6
  • +1
49 Comments
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

by:ralmada
Comment Utility
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

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
you should be ordering by

ORDER BY o.OrderID, o.EventTime

0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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

by:dbaSQL
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

by:ralmada
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

by:ralmada
Comment Utility
So yeah, please my second suggestion a shot http:#a33599306
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
do either of you have any ideas?  i apologize, but this is becoming really very pressing.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
bless you. trying it now,  back shortly
0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
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

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
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 51

Expert Comment

by:HainKurt
Comment Utility
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)

Open in new window

0
 
LVL 51

Expert Comment

by:HainKurt
Comment Utility
I got the difference

LEFT JOIN vs. LEFT OUTER JOIN

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

Author Comment

by:dbaSQL
Comment Utility
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

by:ralmada
Comment Utility
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
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!

 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
and remember, we don't care about the A messages (don't want them)
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

Open in new window

0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
Still can't get the o message up top the list, though.

ORDER BY o.OrderNo, o.messagetype DESC
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo
      AND c.messagetype = COALESCE('X','C')

yeah?
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
well, it seems appropriate for the conditional join,  but my record counts dropped by more than half.  still looking.
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
>>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?

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

Open in new window

0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
If there is an X and a C, how does that guarantee I will use the X, ralmada?
0
 
LVL 41

Expert Comment

by:ralmada
Comment Utility
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

by:dbaSQL
Comment Utility
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

by:dbaSQL
Comment Utility
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

by:ralmada
Comment Utility
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

by:ralmada
Comment Utility
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

by:dbaSQL
Comment Utility
will do.  thank you, ralmada.  
0
 
LVL 17

Author Comment

by:dbaSQL
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

772 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

9 Experts available now in Live!

Get 1:1 Help Now