多连接查询和子查询

Question

表结构：tbl_transaction和tblCustomer之间
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 NULL 

tbl_TransactionDishout

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

关系是有相同cardno。
是否有可能得到的结果如下日期明智记录：

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

日期应该是从，即使它具有零条记录这两个表..

Enrolled   - Total No of records that is summation of Enrolled as Email and Enrolled as Text.  
Enrolled as Email - Where field1 = 'Email' from tblCustomer table 
Enrolled as Text - Where field1 = 'cell phone' from tblCustomer table 
Deals Redeemed - Where field14 <> 'Test Card' from tblCustomer table and  
        where DishoutResponsecode = '0000' from tbl_Transaction table 

我现有的查询：

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, 
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 (IsNull(t1.field14, '') <> 'Test Card') 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) 
ORDER BY 
     convert(varchar, CAST(ISNULL(t1.created,t2.created) AS DATETIME), 111) DESC   

最后4-5记录tblCustomer表

created     cardno    field14 
---------------------------------------------------------- 
2012-03-07 10:03:00.034 1234007600101240  
2012-03-05 04:02:00.040 1234007600602122  
2012-03-01 06:25:50.400 1234010400972168  Test Card 
2012-03-01 30:05:30.022 555566669999   Test Card 
2012-03-01 50:50:20.450 666677778888   Test Card  

最后4-5记录tbl_TransactionDisout表

created     VirtualCard   DishoutResponseCode 
----------------------------------------------------------------------- 
2012-03-09 13:18:02.703 1234010400972168 0010 
2012-03-09 13:17:35.307 1234010400972168 0002 
2012-03-09 13:17:14.237 1234010400972168 0007 
2012-03-09 13:16:57.650 1234010400972168 0002 
2012-03-08 21:13:57.137 1234010400475686 0000 
2012-03-08 16:50:38.273 1234010400972168 0002 
2012-03-08 16:50:26.070 1234010400972168 0007 
2012-03-08 16:49:49.793 1234010400972168 0002  

所以在这个只有一张卡具有响应码“0000”，也没有“测试卡”。但我我得到所有的代码='0000'的卡，也有'测试卡'，这意味着field14无法比较，因为它是从不同的表格和不同的日期。

Answer 1

这是我回来的结果在使用您在主要问题中指定的数据时使用此查询：

SELECT 
    convert(varchar, CAST(ISNULL(t1.created,t2.created) AS DATETIME), 111) created, 
    COUNT(CASE WHEN (t1.field1 = 'Email' or t1.field1 = 'Cell Phone') and (t1.field14 <> 'Test Card' or t1.field14 is null) THEN 1 END) Enrolled, 
    COUNT(CASE WHEN t1.field1 = 'Email'   and (IsNull(t1.field14, '') <> 'Test Card') THEN 1 END) Enrolled_as_Email, 
    COUNT(CASE WHEN t1.field1 = 'Cell Phone'  and (IsNull(t1.field14, '') <> 'Test Card') THEN 1 END) Enrolled_as_Cell, 
    COUNT(CASE WHEN t2.DishoutResponseCode = '0000' and (IsNull(t1.field14, '') <> 'Test Card') 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) 
ORDER BY 
    convert(varchar, CAST(ISNULL(t1.created,t2.created) AS DATETIME), 111) DESC 

结果：

created      Enrolled Enrolled_as_Email Enrolled_as_Cell Deals_Redeemed 
------------------------------ ----------- ----------------- ---------------- -------------- 
2012/03/09      0   0     0    0 
2012/03/08      0   0     0    1 
2012/03/07      1   0     1    0 
2012/03/05      1   0     1    0 
2012/03/01      0   0     0    0