当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - Add SubQuery in existing subquery

问题描述:

Table Structures:

tblCustomer

Customer_id created field1 field2 cardno

--------------------------------------------------------

1014 Test1 Cell Phone 123146 1234567890

1015 Test2 Email [email protected] 2345678891

tbl_TransactionDishout

Trnx_id offerNo TerminalID Created cardno

-------------------------------------------------------------------

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

My current query is something like this:

select created,

count(field1) Enrolled,

count(case field1 when 'E-mail' then 1 end) Enrolled_as_Email,

count(case field1 when 'Cell Phone' then 1 end) Enrolled_as_Cell

from tblCustomer c

group by created

order by created desc

But It's giving me the result of the date contained only in tblCustomer table..

Now, How to get Deals_redeemed..?

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

网友答案:

In my understanding table tbl_TransactionDishout is an offer, and if it is followed through a record will be inserted into tblCustomers; if not, nothing will change. So deals redeemed is count of non-existing records in tblCustomers for given cardno:

select t.created,
   count(c.field1) Enrolled,
   count(case c.field1 when 'E-mail' then 1 end) Enrolled_as_Email,
   count(case c.field1 when 'Cell Phone' then 1 end) Enrolled_as_Cell,
   count(case when c.field1 is null then 1 end) [Deals Redeemed]
from tbl_TransactionDishout t left join tblCustomer c 
   on t.cardno = c.cardno
group by t.created
order by t.created desc

EDIT: c.created changed to t.created

分享给朋友:
您可能感兴趣的文章:
随机阅读: