A director came to me asking if a set based approach could be used to find the fallout for trades and receipts.  Even though they handed the work off already.  The developer  used COBOL to compare the 2 file and did "spin-up" processing to match a trade to a receipt.  Only probalem is that is totally arbitrary since the "key" was basicall generic and did not exactly marry the receipt to the trade.  So their "fallout" was based on LILO.  They considered everything else as a match.

What they really needed to do was identify target population that have "fallout" and address those populations as a whole.  Which they have not I don't think..mostly because they believe they have their solutiion.  In any case, this, in my own opinion (MOO) is what should have been done.

USE Northwind
GO

CREATE TABLE myTrades99   (myKey int, myDate datetime)
CREATE TABLE myReceipts99 (myKey int, myDate datetime)
GO

INSERT INTO myTrades99(myKey, myDate)
SELECT 1, '1/1/2006' UNION ALL
SELECT 1, '2/1/2006' UNION ALL
SELECT 1, '3/1/2006' UNION ALL
SELECT 2, '1/1/2006' UNION ALL
SELECT 3, '1/1/2006' UNION ALL
SELECT 3, '2/1/2006' UNION ALL
SELECT 3, '3/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 3, '5/1/2006'
GO

INSERT INTO myReceipts99(myKey, myDate)
SELECT 2, '1/1/2006' UNION ALL
SELECT 3, '1/1/2006' UNION ALL
SELECT 3, '2/1/2006' UNION ALL
SELECT 3, '3/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 3, '5/1/2006' UNION ALL
SELECT 3, '1/1/2006' UNION ALL
SELECT 3, '2/1/2006' UNION ALL
SELECT 3, '3/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 3, '4/1/2006' UNION ALL
SELECT 4, '5/1/2006'
GO

   SELECT COALESCE(XXX.myKey, YYY.myKey) as myKey, TradeCount, ReceiptCount
     FROM (
     SELECT 'Trade' AS Source, myKey, COUNT(*) AS TradeCount
       FROM myTrades99
   GROUP BY myKey) AS XXX
FULL JOIN (
     SELECT 'Receipt' AS Source, myKey, COUNT(*) AS ReceiptCount
       FROM myReceipts99
   GROUP BY myKey) AS YYY
       ON XXX.myKey = YYY.myKey
    WHERE COALESCE(ReceiptCount,0) <> COALESCE(TradeCount,0)

-- Returns the fallout population with Set based processing

-- myKey       TradeCount  ReceiptCount
-- ----------- ----------- ------------
-- 1           3           NULL
-- 3           5           10
-- 4           NULL        1
--
-- (3 row(s) affected)

Go

SET NOCOUNT OFF
DROP TABLE myTrades99, myReceipts99
GO