x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Finding Trade and Receipt fallout

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

 

Print | posted on Wednesday, September 20, 2006 1:31 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: Finding Trade and Receipt fallout

Brett -- FULL JOIN!!! ugly !!!

all you need is UNION ALL ....

select myKey, sum(ReceiptCount), sum(TradeCount)
from
(
select myKey, 1 as ReceiptCount, 0 as TradeCount
from myReceipts99
union all
select myKey, 0 as ReceiptCount, 1 as tradeCount
from myTrades99
) x
group by myKey
having sum(ReceiptCount) <> sum(TradeCount)
9/20/2006 3:08 PM | Jeff
Gravatar

# re: Finding Trade and Receipt fallout

Jeff instead of making comments like "FULL JOIN!!! Ugly!!!" which is totally still you.
Try asking why or understanding why Brett did that. Be a little bit more understanding, no one is as smart as you..because well you know you are just so SMART...

Comments like that again show how you truly cannot work with others. Make your suggestions a little bit more friendlier and not so cocky...
9/22/2006 9:38 AM | Jon
Gravatar

# re: Finding Trade and Receipt fallout

Very nice Jeff, and it returns the same

myKey
----------- ----------- -----------
1 0 3
3 10 5
4 1 0

Jon: Jeff understands...I was just looking at it from a different angle
9/26/2006 1:33 PM | Brett
Gravatar

# re: Finding Trade and Receipt fallout

Whoa see the blog has changed, looks a lot better Brett.
9/27/2006 2:06 PM | Jon
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET