Hello all,

I have a database with trade information. I have written a stored
procedure (code at end of post) which pulls trades from a range of
dates. There is a field called "Match ID" which is used to group
together trades.

Now, I need to modify this SP so that it pulls trades in the date range
AND any trades with Match IDs that match those of the pulled trades.

So, right now if I enter 12/20/04-12/20/04 for the SP, it returns all
trades between those dates. I need it to return all those trades, along
with any trades from ANY dates that have a match ID in common with the
query results.

Any ideas? I would use a union or something but I'm not sure what the
syntax would be.

Help is appreciated!

Thanks,

Jim Armstrong

CODE POSTED BELOW

SELECT TOP 100 PERCENT dbo.tblTradeAccount.AccountingCode AS
TradeAccount, dbo.tblOrders.TicketNum, dbo.tblOrders.TradeDate,
dbo.tblOrders.SettleDate, NULL AS
ProductionMonth, dbo.tblOrders.RepID, dbo.tblOrders.AcctNum,
dbo.tblAccounts.Shortname, dbo.tblOrders.Quantity,
dbo.tblCUSIP.Factor, dbo.tblOrders.BuySell,
dbo.tblOrders.CUSIP, dbo.tblCUSIP.Issuer, dbo.tblCUSIP.PoolNum,
dbo.tblCUSIP.Coupon,
dbo.tblOrders.FixAdj, dbo.tblOrders.Price,
dbo.tblOrders.RepSC, '=(H:H*I:I*Q:Q)/100' AS Markup, 'PTMSA' AS
ProdType, dbo.tblOrders.DeskSC,
dbo.tblOrders.RepCarry, '=(H:H*I:I*T:T)/100' AS
DeskMarkup, dbo.tblOrders.MatchID,

'=IF(K:K="B",((I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' AS
TotalPrincipal, dbo.tblOrders.CancelCorrect,
dbo.tblOrders.OriginalTrade,
dbo.tblOrders.TradeTime, dbo.tblOrders.Rep2ID,
dbo.tblOrders.Rep2SC
FROM dbo.tblOrders INNER JOIN
dbo.tblCUSIP ON dbo.tblOrders.CUSIP =
dbo.tblCUSIP.CUSIP INNER JOIN
dbo.tblAccounts ON dbo.tblOrders.AcctNum =
dbo.tblAccounts.AcctNum INNER JOIN
dbo.tblTradeAccount ON dbo.tblOrders.TradeAccount
= dbo.tblTradeAccount.TradeAccount
WHERE (CONVERT(varchar(10), dbo.tblOrders.TradeDate, 101) >=
CONVERT(varchar(10), @begindate, 101)) AND (CONVERT(varchar(10),
dbo.tblOrders.TradeDate, 101) <=
CONVERT(varchar(10), @enddate, 101)) OR
(CONVERT(varchar(10), dbo.tblOrders.TradeTime,
101) >= CONVERT(varchar(10), @begindate, 101)) AND
(CONVERT(varchar(10),
dbo.tblOrders.TradeTime, 101) <=
CONVERT(varchar(10), @enddate, 101))
ORDER BY dbo.tblOrders.CancelCorrect,
dbo.tblTradeAccount.AccountingCode, dbo.tblOrders.MatchID,
dbo.tblOrders.BuySell, dbo.tblOrders.TicketNum

Hi

You could try something like:

SELECT T.AccountingCode AS TradeAccount,
O.TicketNum,
O.TradeDate,
O.SettleDate,
NULL AS ProductionMonth,
O.RepID,
O.AcctNum,
A.Shortname,
O.Quantity,
C.Factor,
O.BuySell,
O.CUSIP,
C.Issuer,
C.PoolNum,
C.Coupon,
O.FixAdj,
O.Price,
O.RepSC,
'=(H:H*I:I*Q:Q)/100' AS Markup,
'PTMSA' AS ProdType,
O.DeskSC,
O.RepCarry,
'=(H:H*I:I*T:T)/100' AS DeskMarkup,
O.MatchID,
'=IF(K:K="B",((I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' AS TotalPrincipal,
O.CancelCorrect,
O.OriginalTrade,
O.TradeTime,
O.Rep2ID,
O.Rep2SC
FROM dbo.tblOrders O
INNER JOIN dbo.tblCUSIP C ON O.CUSIP = C.CUSIP
INNER JOIN dbo.tblAccounts A ON O.AcctNum = A.AcctNum
INNER JOIN dbo.tblTradeAccount T ON O.TradeAccount = T.TradeAccount
WHERE O.MATCHID IN ( SELECT MATCHID
FROM dbo.tblOrders
WHERE (CONVERT(char(8),
TradeDate, 112) >= CONVERT(char(8), @begindate, 112))
AND (CONVERT(char(8), TradeDate,
112) <= CONVERT(char(8), @enddate, 112))
OR (CONVERT(char(8), TradeTime, 112) >= CONVERT(char(8), @begindate, 112))
AND (CONVERT(char(8), TradeTime, 112) <= CONVERT(varchar(8), @enddate,
112)) )
ORDER BY O.CancelCorrect, T.AccountingCode, O.MatchID, O.BuySell,
O.TicketNum

Using date format ccyymmdd will give the correct alphabetic ordering for
your dates, but if you put in appropriate @begindate and @enddates then you
should not need to truncate or convert to character data.

John


"Jim Armstrong" <armstrongjc@hotmail.com> wrote in message
news:1103565851.396172.55530@c13g2000cwb.googlegro ups.com...
> Hello all,
>
> I have a database with trade information. I have written a stored
> procedure (code at end of post) which pulls trades from a range of
> dates. There is a field called "Match ID" which is used to group
> together trades.
>
> Now, I need to modify this SP so that it pulls trades in the date range
> AND any trades with Match IDs that match those of the pulled trades.
>
> So, right now if I enter 12/20/04-12/20/04 for the SP, it returns all
> trades between those dates. I need it to return all those trades, along
> with any trades from ANY dates that have a match ID in common with the
> query results.
>
> Any ideas? I would use a union or something but I'm not sure what the
> syntax would be.
>
> Help is appreciated!
>
> Thanks,
>
> Jim Armstrong
>
> CODE POSTED BELOW
>
> SELECT TOP 100 PERCENT dbo.tblTradeAccount.AccountingCode AS
> TradeAccount, dbo.tblOrders.TicketNum, dbo.tblOrders.TradeDate,
> dbo.tblOrders.SettleDate, NULL AS
> ProductionMonth, dbo.tblOrders.RepID, dbo.tblOrders.AcctNum,
> dbo.tblAccounts.Shortname, dbo.tblOrders.Quantity,
> dbo.tblCUSIP.Factor, dbo.tblOrders.BuySell,
> dbo.tblOrders.CUSIP, dbo.tblCUSIP.Issuer, dbo.tblCUSIP.PoolNum,
> dbo.tblCUSIP.Coupon,
> dbo.tblOrders.FixAdj, dbo.tblOrders.Price,
> dbo.tblOrders.RepSC, '=(H:H*I:I*Q:Q)/100' AS Markup, 'PTMSA' AS
> ProdType, dbo.tblOrders.DeskSC,
> dbo.tblOrders.RepCarry, '=(H:H*I:I*T:T)/100' AS
> DeskMarkup, dbo.tblOrders.MatchID,
>
> '=IF(K:K="B",((I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' AS
> TotalPrincipal, dbo.tblOrders.CancelCorrect,
> dbo.tblOrders.OriginalTrade,
> dbo.tblOrders.TradeTime, dbo.tblOrders.Rep2ID,
> dbo.tblOrders.Rep2SC
> FROM dbo.tblOrders INNER JOIN
> dbo.tblCUSIP ON dbo.tblOrders.CUSIP =
> dbo.tblCUSIP.CUSIP INNER JOIN
> dbo.tblAccounts ON dbo.tblOrders.AcctNum =
> dbo.tblAccounts.AcctNum INNER JOIN
> dbo.tblTradeAccount ON dbo.tblOrders.TradeAccount
> = dbo.tblTradeAccount.TradeAccount
> WHERE (CONVERT(varchar(10), dbo.tblOrders.TradeDate, 101) >=
> CONVERT(varchar(10), @begindate, 101)) AND (CONVERT(varchar(10),
> dbo.tblOrders.TradeDate, 101) <=
> CONVERT(varchar(10), @enddate, 101)) OR
> (CONVERT(varchar(10), dbo.tblOrders.TradeTime,
> 101) >= CONVERT(varchar(10), @begindate, 101)) AND
> (CONVERT(varchar(10),
> dbo.tblOrders.TradeTime, 101) <=
> CONVERT(varchar(10), @enddate, 101))
> ORDER BY dbo.tblOrders.CancelCorrect,
> dbo.tblTradeAccount.AccountingCode, dbo.tblOrders.MatchID,
> dbo.tblOrders.BuySell, dbo.tblOrders.TicketNum
>

Jim Armstrong (armstrongjc@hotmail.com) writes:
> WHERE (CONVERT(varchar(10), dbo.tblOrders.TradeDate, 101) >=
> CONVERT(varchar(10), @begindate, 101)) AND (CONVERT(varchar(10),
> dbo.tblOrders.TradeDate, 101) <=
> CONVERT(varchar(10), @enddate, 101)) OR
> (CONVERT(varchar(10), dbo.tblOrders.TradeTime,
> 101) >= CONVERT(varchar(10), @begindate, 101)) AND
> (CONVERT(varchar(10),
> dbo.tblOrders.TradeTime, 101) <=
> CONVERT(varchar(10), @enddate, 101))
> ORDER BY dbo.tblOrders.CancelCorrect,
> dbo.tblTradeAccount.AccountingCode, dbo.tblOrders.MatchID,
> dbo.tblOrders.BuySell, dbo.tblOrders.TicketNum

As John pointed out, to compare dates in charcter form, you must use
YYYYMMDD.

But there is more to it. Presumably you have some indexes on
Orders.TradeDate, but if you stick that date into a function, you
are precluding use of that index.

I don't know what the difference is between TradeDate and TradeTime,
but I would assume that TradeDate is just the date with midnight
in the time portion. In such case a simple

O.TradeDate BETWEEN @begindate AND @startdate

would do, presuming that the parameters, too, are date-only values.

Really what purpose the condition on TradeTime serves I don't know, but
I would assume TradeTime to be within TradeDate, in which case that
condition is simply redundant.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks for the help guys, I got it using a method similar to Tom's reply
- selecting the trades where matchid is in a subquery.

To answer the questions about the date formats, I had some trouble
comparing when I just used a "between @startdate and @enddate" - after
tweaking it for awhile I got it to work with the convert statements, so
I left it alone. The TradeDate is the actual date the trade took place.
TradeTime is a last activity time that gets reset whenever a trade is
edited, so they can be completely different dates...

Thanks for the help, guys, I always learn something from you gurus!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!