JOINs not giving expected results in SQL

I have two tables Table F and Table D.

Table F gives details about Financial Transactions of an Account (Payment, Toll, Interest). Table D shows Delinquency Status (if user has not paid into account). It shows different Status Codes. 0, 1, 2, 11 (which mean different things).

My tables structures are as follows:

Table F
============
------------------------------------------- 
|AccountId|TypeCode |BusinessDate|TransAmt|
------------------------------------------- 
|12345    | PYMT    |2016-06-22  |   10.54|
------------------------------------------- 
|12345    | TOLL    |2016-06-15  |   04.00|
------------------------------------------- 
|12345    | INTR    |2016-05-26  |   01.66|
------------------------------------------- 
|12345    | TOLL    |2016-04-10  |   04.00|
------------------------------------------- 

Table D
==========
-----------------------------------------------
|AccountId|FromID|ToID|CreatedDate            |
----------------------------------------------- 
|12345    |     0|   2|2016-12-25 00:12:44.453|
----------------------------------------------- 
|12345    |     2|   1|2017-04-02 07:16:46.770|
----------------------------------------------- 
|12345    |     1|  11|2017-07-12 16:16:36.747|
-----------------------------------------------

I am trying to run code to find the SUM of all payments made on an account AFTER the Account went into Status Code 11 (ToID)

My code:

SELECT F.AccounttID
      ,ISNULL(SUM(TransAmt), 0) Payments

FROM F INNER JOIN D ON F.AccounttID = D.AccountId

WHERE AccountId = 12345   
  AND TypeCode = 'PYMT'   
  AND F.BusinessDate >= D.CreatedDate  
  AND ToID = 11 
--AND F.BusinessDate >= (SELECT CreatedDate FROM D WHERE F.AccounttID = D.AccountId AND ToID = 11)    

GROUP BY F.AccountID, F.TypeCode

The results I am getting are:

--------------------
|AccountID|Payments|
--------------------
|         |        |
--------------------

The results I would like are:

--------------------
|AccountID|Payments|
--------------------
|   12345|     0.00|
--------------------

Answer

You’re problem is there are no matching results with your where criteria. I think you’re actually looking to use conditional aggregation. I’ve also used an outer join in case there’s no match:

SELECT F.AccounttID
      ,SUM(CASE WHEN F.BusinessDate >= D.CreatedDate THEN F.TransAmt ELSE 0 END) Payments
FROM F 
    LEFT JOIN D ON F.AccounttID = D.AccountId AND D.ToId = 11
WHERE F.AccountId = 12345   
    AND F.TypeCode = 'PYMT'   
GROUP BY F.AccountID

Advertisement

Answer