CODEWITHSUNDEEP
sqlsql-servert-sql
Mark
Mark

Reputation: 678

Comparing values from two SQL SELECTs

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)

Upvotes: 0

Views: 86

Answers (5)

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

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

Upvotes: 1

Christian Barron
Christian Barron

Reputation: 2755

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

Upvotes: 0

gotqn
gotqn

Reputation: 43636

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.

Upvotes: 0

Deepshikha
Deepshikha

Reputation: 10264

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

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

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.

Upvotes: 0

Related Questions