Reputation: 17183
Table Structures:
tblCustomer
Customer_id created field1 field2 cardno field14
------------------------------------------------------------------------------------------------
1014 2010-05-25 12:51:59.547 Cell Phone [email protected] 1234567890 Test Card
1015 2010-08-15 12:51:59.547 Email [email protected] 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...
Upvotes: 1
Views: 147
Reputation: 86706
Based on what you have said in the comments section, I think you need to change one snippet of code...
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
Upvotes: 3