CODEWITHSUNDEEP
sql-server
Tilting Code
Tilting Code

Reputation: 137

Get records and counts from two tables where counts do not match

I know how to get records from a table that are not in another table. However, this is something I cannot solve. I have a view and a table. One being a temp table created with rows from the view. However, due to record manipulation I now have missing records and for some the counts no longer match. For instance where I have 20 SSNs from the original I now have 17 or say 24 records in the temp and all should match.

There's not a lot of this happening but there's thousands or records and going through them all manually is too tedious.

Is there a way to get the records and counts where the counts do not match? And can the results include the counts from each table and the table name?

Here's what I have:

Select vw.UPC as [View], COUNT(vw.UPC) as [view Amount], tb.UPC as [Temp], COUNT(tb.UPC) as [temp Amount]
From #tbTransfOUT tb
JOIN vwTrans vw
ON tb.UPC = vw.UPC
Where vw.UPC NOT IN (Select vw2.UPC
                    From vwTrans  vw2
                    JOIN #tbTransfOUT tb2
                    ON tb2.UPC = vw2.UPC
                    WHERE (Select COUNT(UPC) From #tbTransfOUT Group By UPC) =
                           (Select COUNT(UPC) From vwTrans  Group By UPC)
Group By vw.UPC
Order By vw.UPC, COUNT(vw.UPC), tb.UPC, COUNT(tb.UPC)

If Possible I would like to include the source name (View or temp table) instead of the the extra columns. This of course is part of the code which is run at the end of the procedure to compare record counts.

Upvotes: 0

Views: 47

Answers (1)

Adam Jacobson
Adam Jacobson

Reputation: 564

I believe the below should work. You can test each CTE separately. The full outer join finds situations where there are no records for a given UPC in one of the tables

WITH VWCount (UPC, VWCount)
as
(SELECT UPC, COUNT(*) FROM vwTrans
  GROUP BY UPC),
TMPCount (UPC, TMPCount) AS
(SELECT UPC, COUNT(*) FROM #tbTransFout 
 GROUP BY UPC)

SELECT vw.UPC, vw.VWCount, tmp.UPC,  tmp.TMPCount
  FROM VWCount vw
  FULL OUTER JOIN TMPCount tmp
  ON vw.UPC = tmp.UPC
  WHERE COALESCE(vw.VWCount,0) <> COALESCE(tmp.TMPCount,0)

Upvotes: 1

Related Questions