时间戳

首页 > 解决方案 > SQL where between date 子句引用多个日期范围

sql-server - SQL where between date 子句引用多个日期范围

问题描述

我有两个要合并的 SQL 客户表。 

CustomerDate:
Column 1: customerkey int
Column 2: RecentPurchase smalldatetime- the date of their most recent purchase
Column 3: PriorYear smalldatetime - the RecentPurchase date minus a year

CustomerTrans:
Column 1: Transactionkey int
Column 2: customerkey int
Column 3: TransactionDate smalldatetime- Date of Transaction
Column 4: Amount - Total Amount Spent

我想获取客户在最近一次购买和上一年日期之间花费的总金额。我已经尝试了以下查询，但它没有返回任何结果。

select t.customerkey, 
       sum(t.amount)
from customertrans t
inner join customerdate d on t.customerkey = d.customerkey
where  TransactionDate between RecentPurchase and PriorYear
group by t.customerkey

如何运行查询，以便每个客户 TransactionDate 由 CustomerDate 表中的日期确定？

标签： sql-servertsql

解决方案

也许你只是想要：

where TransactionDate between PriorYear and RecentPurchase

代替 ：

where TransactionDate between RecentPurchase and PriorYear

推荐阅读