在SQL中查询8月活跃和9月不活跃的客户
在SQL中查询8月活跃和9月不活跃的客户
提问于 2015-10-08 20:17:42
请帮我找到在8月15日活跃的客户(就交易量而言)和在SEP 15中不活跃的客户。
查询需要超过45分钟的..Kindly帮助。
SELECT DISTINCT C.CustomerCode--,x.CustomerCode
FROM Customer.Customer(nolock) c
INNER JOIN Customer.Card (nolock)cd ON c.CustomerId=cd.CustomerId
INNER JOIN Trans.vwValidRawTransactions rt (nolock) ON rt.AccountNumber=cd.CardNumber AND rt.AccountTypeId=3
where c.CustomerCode not in (
SELECT DISTINCT ca.customercode
FROM Customer.customer Ca (nolock)
INNER JOIN Customer.Card cd (nolock) ON ca.CustomerId=cd.CustomerId
INNER JOIN trans.vwValidRawTransactions ra (nolock) ON cd.CardNumber=ra.AccountNumber AND ra.AccountTypeId=3 AND ra.IsLive=1
WHERE Ra.TransactionDate>='01-09-2015' AND Ra.TransactionDate <'01-10-2015' )
and rt.TransactionDate>='01-08-2015' and rt.TransactionDate<'01-09-2015'回答 3
Stack Overflow用户
发布于 2015-10-08 20:20:48
尝试使用between，
SELECT DISTINCT C.CustomerCode,
x.CustomerCode
FROM Customer.Customer(NOLOCK) c
INNER JOIN Customer.Card (NOLOCK)cd
ON c.CustomerId = cd.CustomerId
INNER JOIN Trans.vwValidRawTransactions rt(NOLOCK)
ON rt.AccountNumber = cd.CardNumber
AND rt.AccountTypeId = 3
WHERE c.CustomerCode NOT
IN (SELECT DISTINCT ca.customercode
FROM Customer.customer Ca(NOLOCK)
INNER JOIN Customer.Card cd(NOLOCK)
ON ca.CustomerId = cd.CustomerId
INNER JOIN trans.vwValidRawTransactions ra(NOLOCK)
ON cd.CardNumber = ra.AccountNumber
AND ra.AccountTypeId = 3
AND ra.IsLive = 1
WHERE Ra.TransactionDate BETWEEN '01-09-2015' AND '01-10-2015')
AND rt.TransactionDate BETWEEN '01-08-2015' AND '01-09-2015'Stack Overflow用户
发布于 2015-10-08 20:23:38
你的问题有点不清楚，因为你的问题比问题所问的复杂得多。
如果您正在查看transaction表，则可以使用aggregation和having子句执行所需的操作：
SELECT rt.AccountNumber
FROM Trans.vwValidRawTransactions rt (nolock)
WHERE rt.AccountTypeId = 3 AND
rt.TransactionDate >= '2015-08-01' AND
rt.TransactionDate < '2015-10-01'
GROUP BY rt.AccountNumber
HAVING MONTH(MAX(rt.TransactionDate)) = 8;也就是说，取这两个月的交易。然后选择最大日期为8月的月份的客户。这些客户在8月份有交易，但在9月份没有。
请注意ISO标准日期格式的使用。您应该在SQL代码中使用标准日期格式。
我不确定查询的其余部分应该做什么。
Stack Overflow用户
发布于 2015-10-08 20:30:47
尝尝这个
SELECT DISTINCT C.CustomerCode--,x.CustomerCode
FROM Customer.Customer(nolock) c
INNER JOIN Customer.Card (nolock)cd ON c.CustomerId=cd.CustomerId
INNER JOIN Trans.vwValidRawTransactions rt (nolock)
ON rt.AccountNumber=cd.CardNumber AND rt.AccountTypeId=3 AND
((rt.IsLive =1 and rt.TransactionDate BETWEEN '01-08-2015' AND '01-09-2015')
or (rt.IsLive !=1 and rt.TransactionDate BETWEEN '01-09-2015' AND '01-10-2015'))页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/33015572
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号