real quick problem with a join (urgent)

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

LVL 17
dbaSQLAsked:
Who is Participating?
 
ralmadaConnect With a Mentor Commented:
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
 
dbaSQLAuthor Commented:
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
 
ralmadaCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ralmadaCommented:
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
 
HainKurtSr. System AnalystCommented:
you should be ordering by

ORDER BY o.OrderID, o.EventTime

0
 
HainKurtSr. System AnalystCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
ralmadaCommented:
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
 
dbaSQLAuthor Commented:
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
 
ralmadaCommented:
So yeah, please my second suggestion a shot http:#a33599306
0
 
HainKurtSr. System AnalystCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
do either of you have any ideas?  i apologize, but this is becoming really very pressing.
0
 
ralmadaCommented:
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
 
dbaSQLAuthor Commented:
bless you. trying it now,  back shortly
0
 
HainKurtSr. System AnalystCommented:
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
 
LowfatspreadCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
I got the difference

LEFT JOIN vs. LEFT OUTER JOIN

but they are same, OUTER is optional value...
0
 
dbaSQLAuthor Commented:
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
 
ralmadaCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
and remember, we don't care about the A messages (don't want them)
0
 
ralmadaCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
Still can't get the o message up top the list, though.

ORDER BY o.OrderNo, o.messagetype DESC
0
 
ralmadaCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
LEFT JOIN dbo.canceltable c ON o.OrderNo = c.OrderNo
      AND c.messagetype = COALESCE('X','C')

yeah?
0
 
dbaSQLAuthor Commented:
well, it seems appropriate for the conditional join,  but my record counts dropped by more than half.  still looking.
0
 
ralmadaCommented:
>>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
 
dbaSQLAuthor Commented:
If there is an X and a C, how does that guarantee I will use the X, ralmada?
0
 
ralmadaCommented:
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
 
dbaSQLAuthor Commented:
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
 
dbaSQLAuthor Commented:
i got it:

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

back in a bit with status
0
 
ralmadaCommented:
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
 
ralmadaCommented:
ok, I guess you figured it out. I would still use the select max(eventtime) rather than the plain select eventtime
 
0
 
dbaSQLAuthor Commented:
will do.  thank you, ralmada.  
0
 
dbaSQLAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.