-1

I have two tables.

Table 1 : 'DQ' is the golden source of information Table 2 : 'PS' is whats currently in the DB.

What I would like to do is compare AMOUNT from the 'DQ' table vs VALIDATED in the 'PS' Table

As you can see although the column names between two tables are different there is a match:

dq.Account_Number =  PS.Account_Code__C
dq.AMOUNT =  PS.VALIDATED

Please note that I may have Account_Number / Account_Code__C in one table and not the other.

Table 1

--//AUM 1M
 SELECT  
   Account_Number,
   sum(AMOUNT)
 FROM DQ
   where Account_Number = '1234'
   group by Account_Number

Table 2

--//validated 1M
 SELECT  
   Account_Code__C,
   sum(VALIDATED)
 FROM PS
   where Account_Code__C = '1234'
   group by Account_Code__C

Expected outcome:

Expected Outcome

Other possible scenarios:

enter image description here

I have written the following query:

  SELECT DISTINCT 
    b.Account_Code__C,
    Amount,
    Validated
FROM (
    SELECT DISTINCT 
    Account_Number, 
    sum(AMOUNT) as Amount
    FROM DQ
    WHERE 1=1
    group by Account_Number
   --// AND Account_Number=@Account_Code
)a
FULL JOIN (
    SELECT DISTINCT 
    Account_Code__C, 
    sum(VALIDATED) as 
    FROM PS
    WHERE 1=1
    group by Account_Code__C
   --// AND Account_Code__C=@Account_Code
)b ON a.Account_Number=b.Account_Code__C
WHERE 1=1
and Account_Code__C = '1234'

I would like someone to validate if possible.

1

Overall, your query will work as you describe, but I wanted to show you a possible re-write that looks cleaner, as well as to point out some possible issues that you may want to address first.

  • The use of DISTINCT with GROUP BY is redundant.
  • Are Account_Number and Account_Code_C the same datatype?
  • Is the AMOUNT and VALIDATED the same datatype? Are they integer or decimal/float?
  • You need to (as I have done) coalesce the values of Account_Number and Account_Code_C (or select both) or when you have an entry in one table but not in another, it won't display correctly).

I have included sample WHERE clauses for you. Putting the specific account you want in the last where should be sufficient to limit the result set for the two CTE's.

For best performance, is there an index on Account_Number / Account_Code_C, with an included column of the AMOUNT / VALIDATED ?

;WITH CTE_DQ AS
    (
    SELECT D.Account_Number
        , SUM(D.AMOUNT) AS Amount
    FROM DQ AS D
    GROUP BY D.Account_Number
    )
, CTE_PS AS
    (
    SELECT P.Account_Code_C
        , SUM(P.VALIDATED) AS Amount_Validated
    FROM PS AS P
    GROUP BY P.Account_Code_C
    )
SELECT COALESCE(D.Account_Number, P.Account_Code_C) AS Account
    , D.AMOUNT
    , P.Amount_Validated
FROM CTE_DQ AS D
    FULL OUTER JOIN CTE_PS AS P ON P.Account_Code_C = D.Account_Number
-- WHERE COALESCE(D.AMOUNT, CONVERT(DECIMAL(10, 2), 0.0)) <> COALESCE(P.Amount_Validated, CONVERT(DECIMAL(10, 2), 0.0)) --To find only the differences (may need to change the datatype of the default.
-- WHERE COALESCE(D.Account_Number, P.Account_Code_C) = '1234'  --To find for a specific account.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.