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