Comparing values from two SQL SELECTs

Question

In the following two SELECT statements, I'd like to compare the outputs from these two to get the IDs of the amounts don't match.

The GROUP BY and SUMs in the first statement seem to be complicating things:

SELECT CreditAHID,
SUM(Debit) as CCDebit,
SUM(Credit) as CCCredit,
FROM FS_2015_June.dbo.CCs
GROUP BY CreditAHID

SELECT ID as CreditAHID,
JuneDebit as AHDebit,
JuneCredit as AHCredit,
FROM FS_2015_2016.dbo.AHs

Is there any way to combine them to something like this?

SELECT ID ... WHERE (CCDebit != AHDebit) OR (CCCredit != AHCredit)

Answer 1

Try this query below:

SELECT
    ResultSet1.*
    , ResultSet2.*
FROM (
    SELECT CreditAHID
        ,SUM(Debit) AS CCDebit
        ,SUM(Credit) AS CCCredit
    FROM FS_2015_June.dbo.CCs
    GROUP BY CreditAHID
    ) ResultSet1
INNER JOIN (
    SELECT ID AS CreditAHID
        ,JuneDebit AS AHDebit
        ,JuneCredit AS AHCredit
    FROM FS_2015_2016.dbo.AHs
    ) ResultSet2 ON ResultSet1.CreditAHID = ResultSet2.CreditAHID
WHERE ResultSet1.CCDebit <> ResultSet2.AHDebit
    AND ResultSet1.CCCredit <> ResultSet2.AHCredit

This query will return the combination of both results where a CreditAHID matches in both results and where the CCDebit and CCCredit is not equal to AHDebit and AHCredit respectively.

Answer 2

You can simply write as:

select T1.CreditAHID as [CCs], T2.CreditAHID as [AHs]
from (
SELECT CreditAHID,
SUM(Debit) as CCDebit,
SUM(Credit) as CCCredit,
FROM FS_2015_June.dbo.CCs
GROUP BY CreditAHID) T1
full Join (
SELECT ID as CreditAHID,
JuneDebit as AHDebit,
JuneCredit as AHCredit,
FROM FS_2015_2016.dbo.AHs) T2 
on T1.CreditAHID = T2.CreditAHID
where T1.CCDebit <> AHDebit 
and T1.CCCredit <> T2.AHCredit

Answer 3

You can try it with CTE:

WITH cte as (
SELECT CreditAHID,
SUM(Debit) as CCDebit,
SUM(Credit) as CCCredit,
FROM FS_2015_June.dbo.CCs
GROUP BY CreditAHID
), cte2 as (
SELECT ID as CreditAHID,
JuneDebit as AHDebit,
JuneCredit as AHCredit,
FROM FS_2015_2016.dbo.AHs)

SELECT * FROM cte
UNION
SELECT * FROM cte2
WHERE CCDebit <> AHDebit and CCCredit <> AHCredit

Answer 4

You can use EXCEPT like this:

SELECT CreditAHID,
SUM(Debit) as CCDebit,
SUM(Credit) as CCCredit,
FROM FS_2015_June.dbo.CCs
GROUP BY CreditAHID
EXCEPT
SELECT ID as CreditAHID,
JuneDebit as AHDebit,
JuneCredit as AHCredit,
FROM FS_2015_2016.dbo.AHs

Note, that EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. So, if the second query contains more rows, it should be referred first.

Answer 5

You could try using good old not exists and having like this:

SELECT CreditAHID, SUM(Debit) as CCDebit, SUM(Credit) as CCCredit
   FROM FS_2015_June.dbo.CCs c
GROUP BY CreditAHID
having not exists 
       (SELECT 1
          FROM FS_2015_2016.dbo.AHs
         where ID = c.CreditAHID and 
        (Sum(c.Debit) = JuneDebit or Sum(c.Credit) = JuneCredit))

Here is an example I made in SQL Fiddle: http://sqlfiddle.com/#!6/d02b8/1/0