/*It's a long post, and it can all be copied and pasted into QA.

i have a "TransactionEntries" table, that has multiple entries for a single
transaction.

Example#1
TransactionID,     CurrencyCode,     Bought,    Sold
1,     USCash,     20,     0
1,     CanadianCash,     0,     25

In this case, they took in $20USD, and gave out $25CAD

Example#2
TransactionID,     CurrencyCode,     Bought,    Sold
2,     USCash,     20,     5
2,     CanadianCash,     0,     17

In this case, they took in $20USD, and gave it out as $5USD and $17CAD

They now want to perform searching of transactions that did certain kinds of
exchanges.
All the DDL below sets up test data, and my various attempts at solutions;
but i'm looking for more ideas that run better.

My queries are of the form:
SELECT * FROM Transactions t
WHERE EXISTS (
     SELECT * FROM TransactionEntries te
     WHERE t.TransactionID = te.TransactionID
     AND CurrencyCode = 'USCash'
     AND SellAmount <> 0)
which returns all transactions where they sold some USCash
*/



CREATE DATABASE foo
USE foo
CREATE TABLE Transactions (
TransactionID int NOT NULL,
TransactionDate datetime NOT NULL default getdate(),
CustomerID int NOT NULL
--...
--etc
)
INSERT INTO Transactions (TransactionID, CustomerID)
VALUES (1, 342387)
INSERT INTO Transactions (TransactionID, CustomerID)
VALUES (2, 800028)
INSERT INTO Transactions (TransactionID, CustomerID)
VALUES (3, 5103234)

CREATE TABLE TransactionEntries (
TransactionID int NOT NULL,
CategoryCode varchar(50) NOT NULL,
BuyAmount money NOT NULL,
SellAmount money NOT NULL)

INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (1, 'USCash', 15, 0)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (1, 'USCash', 0.25, 0)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (1, 'CanadianCash', 0, 0)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (1, 'Tokens', 0, 15)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (1, 'Tokens', 0, 2.23)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (2, 'USCash', 0, 0)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (2, 'CanadianCash', 0, 10)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (2, 'CanadianCash', 0, 7.84)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (2, 'Tokens', 25.23, 0)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (2, 'Tokens', 20, 0)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (3, 'USCash', 43.23, 6)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (3, 'CanadianCash', 0, 77.86)
INSERT INTO TransactionEntries (TransactionID, CategoryCode, BuyAmount,
SellAmount)
VALUES (3, 'Tokens', 43.23, 0)

--SELECT * FROM TransactionEntries

--Transactions where USCash was Bought:
SELECT *
FROM Transactions t
WHERE EXISTS (
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'USCash') and (BuyAmount <> 0))
)
/*
TransactionID TransactionDate
CustomerID
------------- ------------------------------------------------------ -----------
1             2006-09-12 13:53:41.273                                342387
3             2006-09-12 13:53:41.273                                5103234

(2 row(s) affected)
*/

--Transactions where CanadianCash was sold:
SELECT *
FROM Transactions t
WHERE EXISTS (
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'CanadianCash') and (SellAmount <> 0))
)

/*
TransactionID TransactionDate
CustomerID
------------- ------------------------------------------------------ -----------
2             2006-09-12 13:53:41.273                                800028
3             2006-09-12 13:53:41.273                                5103234

(2 row(s) affected)
*/

--Transactions where USCash was bought, and CanadianCash was sold
SELECT *
FROM Transactions t
WHERE EXISTS (
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'USCash') and (BuyAmount <> 0))
)
AND EXISTS (
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'CanadianCash') and (SellAmount <> 0))
)
/*
TransactionID TransactionDate
CustomerID
------------- ------------------------------------------------------ -----------
3             2006-09-12 14:14:25.583                                5103234

(1 row(s) affected)
*/

--Transactions where USCash was bought AND sold, and CanadianCash was sold
SELECT *
FROM Transactions t
WHERE EXISTS ( --Check for USCash
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'USCash') AND (BuyAmount <> 0) AND (SellAmount <> 0))
)
AND EXISTS ( --Check for CanadianCash
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'CanadianCash') and (SellAmount <> 0))
)
/*
TransactionID TransactionDate
CustomerID
------------- ------------------------------------------------------ -----------
3             2006-09-12 14:14:25.583                                5103234

(1 row(s) affected)
*/


/*
There are seven CategoryCode in the system that they could filter on,
USCash
CanadianCash
Tokens
Chips
Miscellaneous
MiscellaneousUS
Foreign
and the filter criteria for each can either be
a. Transaction bought at least some of this currency
b. Transaction sold at least some of this currency
c. Tranasaction bought AND sold at least some of this currency
d. Don't care about this currency

So i could have up to seven different EXISTS...
*/
SELECT *
FROM Transactions t
WHERE EXISTS ( --Check for USCash
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'USCash') AND (BuyAmount <> 0) AND (SellAmount <> 0))
)
AND EXISTS ( --Check for CanadianCash
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'CanadianCash') and (SellAmount <> 0))
)
--...
AND EXISTS ( --Check for Foreign
SELECT * FROM TransactionEntries te
WHERE t.TransactionID = te.TransactionID
AND ((CategoryCode = 'Foreign') and (SellAmount <> 0))
)

/*All these exists make the query take a long time (in practice, with
volumes of data), there must be a better way.
What if i made a view:
*/

CREATE VIEW TransactionBoughtSoldByCategoryCode AS
SELECT
TransactionID,
--USCash buy and sell
SUM( CASE
  WHEN (CategoryCode = 'USCash') THEN BuyAmount
  ELSE 0
  END) AS USCashBuy,
SUM( CASE
  WHEN (CategoryCode = 'USCash') THEN SellAmount
  ELSE 0 END) AS USCashSell,
--CanadianCash buy and sell
SUM( CASE
  WHEN (CategoryCode = 'CanadianCash') THEN BuyAmount
  ELSE 0 END) AS CanadianCashBuy,
SUM( CASE
  WHEN (CategoryCode = 'CanadianCash') THEN SellAmount
  ELSE 0 END) AS CanadianCashSell,
--...
--Foreign buy and sell
SUM( CASE
  WHEN (CategoryCode = 'Foreign') THEN BuyAmount
  ELSE 0 END) AS ForeignBuy,
SUM( CASE
  WHEN (CategoryCode = 'Foreign') THEN SellAmount
  ELSE 0 END) AS ForeignSell
FROM TransactionEntries
GROUP BY TransactionID

--Then i could query for:

SELECT *
FROM Transactions
INNER JOIN TransactionBoughtSoldByCategoryCode tbsbcc
ON Transactions.TransactionID = tbsbcc.TransactionID
AND USCashBuy <> 0
AND CanadianCashSell <> 0

/*
Except this also takes too long on the volume of data also.

Can anyone think of a better way?
*/

use master
DROP DATABASE foo

<DIV>&quot;Ian Boyd&quot; &lt;ian.msnews***@avatopia.com&gt; wrote in
post, and it can all be copied and pasted into QA.
You've got a simple query to, it's just slow to group all the rows by
transactionID.

Sounds to me like a good case for using an indexed view:

create VIEW TransactionBoughtSoldByCategoryCode
with schemabinding
AS
SELECT
TransactionID,
--USCash buy and sell
SUM( CASE
  WHEN (CategoryCode = 'USCash') THEN BuyAmount
  ELSE 0
  END) AS USCashBuy,
SUM( CASE
  WHEN (CategoryCode = 'USCash') THEN SellAmount
  ELSE 0 END) AS USCashSell,
--CanadianCash buy and sell
SUM( CASE
  WHEN (CategoryCode = 'CanadianCash') THEN BuyAmount
  ELSE 0 END) AS CanadianCashBuy,
SUM( CASE
  WHEN (CategoryCode = 'CanadianCash') THEN SellAmount
  ELSE 0 END) AS CanadianCashSell,
--...
--Foreign buy and sell
SUM( CASE
  WHEN (CategoryCode = 'Foreign') THEN BuyAmount
  ELSE 0 END) AS ForeignBuy,
SUM( CASE
  WHEN (CategoryCode = 'Foreign') THEN SellAmount
  ELSE 0 END) AS ForeignSell,
COUNT_BIG(*) Rows
FROM dbo.TransactionEntries
GROUP BY TransactionID

go

create unique clustered index iv_TransactionBoughtSoldByCategoryCode
on TransactionBoughtSoldByCategoryCode(TransactionID)


David

A .NET Framework error occurred during execution of user defined r
sql statement (how to)
Update Information in SQL from a VBS script
Return a City in a column on max count from another column in a group by
Need help with select statement
Dynamic SQL load into dynamic table
Converting Orcale DECODE stmt to T-SQL
validate statement before execute with sp_executesql
SQL Select using parameter
Setting up the SQL Server alias programatically