Wrong Output in the FULL OUTER JOIN query

Question

Table Structures:
tblCustomer

Customer_id  created                 field1            field2        cardno       field14
------------------------------------------------------------------------------------------------    
1014         2010-05-25 12:51:59.547 Cell Phone        [e-mail]   1234567890   Test Card
1015         2010-08-15 12:51:59.547 Email             [e-mail]   2345678891

tbl_TransactionDishout

Trnx_id   offerNo   TerminalID      Created                  VirtualCard
-------------------------------------------------------------------
1         1014      170924690436418 2010-05-25 12:51:59.547  1234567890

Is it possible to get the result as below date-wise records:

               Enrolled   Enrolled as Email  Enrolled as Text Deals Redeemed   
<First Date>   7          5                  2                6
<Next Date>    9          3                  6               14

Existing query:

SELECT
  convert(varchar, CAST(ISNULL(t1.created,t2.created) AS DATETIME), 111) created,
  COUNT(CASE WHEN (t1.field1 = 'E-mail' or t1.field1 = 'Cell Phone')  and (t1.field14 <> 'Test Card'  or t1.field14 is null) THEN 1 END) Enrolled,
  t1.field14,
  COUNT(CASE WHEN t1.field1 = 'E-mail'            and (t1.field14 <> 'Test Card'   and t1.field14 is null) THEN 1 END) Enrolled_as_Email,
  COUNT(CASE WHEN t1.field1 = 'Cell Phone'        and (t1.field14 <> 'Test Card'   and t1.field14 is null) THEN 1 END) Enrolled_as_Cell,
  COUNT(CASE WHEN t2.DishoutResponseCode = '0000' and (t1.field14 <> 'Test Card'   and t1.field14 is null) THEN 1 END) Deals_Redeemed
FROM
  tblCustomer AS t1
FULL OUTER JOIN
  tbl_TransactionDishout t2
    ON  t1.cardno = t2.VirtualCard
    AND t1.created = t2.created
GROUP BY
  convert(varchar, CAST(ISNULL(t1.created,t2.created) AS DATETIME),      111),
  t1.field14
ORDER BY
  convert(varchar, CAST(ISNULL(t1.created,t2.created) AS DATETIME), 111) DESC

Now, here I am facing one problem that the fourth column is executing something wrong because I have One record in tbl_TransactionDishout table yesterday which has responsecode = 0000 and also is not a 'Test card' but still I am getting 0 count.

relation between tbl_transaction and tblCustomer is having same cardno...

Answer 1

Based on what you have said in the comments section, I think you need to change one snippet of code...

• What I want is that card should not have field14 as 'Test card'

(t1.field14 <> 'Test Card' and t1.field14 is null)

=>

(t1.field14 <> 'Test Card'  OR  t1.field14 is null)

Build up a logic table to check if you really want AND or if you want OR

field14     | (field14 <> 'Test Card') | (t1.field14 is null) | A OR B | A AND B
--------------------------------------------------------------------------------
'Test Card' |            FALSE         |        FALSE         | FALSE  | FALSE
NULL        |            NULL          |        TRUE          | TRUE   | FALSE
'Any Card'  |            TRUE          |        FALSE         | TRUE   | FALSE

EDIT Follow up to comment

Using OR in the code above can't yield TRUE when Field14 is 'Test Card'. Both tests yield FALSE and so the result must be FALSE.

You need to break things down in stages. Debugging should be done by testing pieces at a time and graddually proving what work to isolate what doesn't. Never try to sort everything out at once, approach things methodically.

Run this test...

SELECT
  *,
  CASE WHEN field14 <> 'Test Card'                     THEN 1 ELSE 0 END     Test1,
  CASE WHEN field14 IS NULL                            THEN 1 ELSE 0 END     Test2,
  CASE WHEN field14 <> 'Test Card' OR  field14 IS NULL THEN 1 ELSE 0 END     1_OR_2,
  CASE WHEN field14 <> 'Test Card' AND field14 IS NULL THEN 1 ELSE 0 END     1_AND_2
FROM
  tblCustomer