需要仅在选定日期计算每个客户的票数，以及数量和总数的每日销售额

Question

我需要计算所选时间段的票证数量，这个票数应该是每个客户。 下表是我有线级数据的表格。

Invoice No |  Customer  | date       | Amount
1                 C1     01/01/2020      500
2                 C2     01/01/2020      600
3                 C3     02/01/2020      200
4                 C2     02/01/2020      200
5                 C1     03/01/2020      400
6                 C1     04/01/2020      300 

我的输出应该如下所示

Customer Name | Date        | InvoiceNo | Total invoice | Total Amount 
C1              01/01/2020    1           3                      900
C1              03/01/2020    5           3                      900
C1              04/01/2020    6           3                      900
C2              01/01/2020    2           2                      800
C2              02/01/2020    4           2                      800
C3              02/01/2020    3           1                      200

我可以从下面的sql查询中获得预期的输出

select A.Customer, B.docN, B.amount AS DocumentTotal,  
A.date AS Date FROM
(select  InvoiceNo,Customer,date from Table1
Where date>=’20200201 00 :00 :00’ and date <=’20200201 00:00:00’
) A
join 
(select Customer,sum(amount) as Amount,count(InvoiceNo) as docN from Table1
where date>=’20200201 00 :00 :00’ and date <=’20200201 00:00:00’
group by Customer) B
on A.Customer =B.Customer 

问题是我将在过滤器中选择">开始日期"和"结束日期"。而我的过滤器在A内，所以B中的发票总值不遵守所选日期。请告诉我如何使用一个日期过滤器，这应该适用于 A 和 B

Answer 1

在下面尝试并告诉我们，这里唯一的事情是你不会得到invoice_numbers

select  Customer,count(A.InvoiceNo) as Total_Invoice,date,sum(B.amount) as Total_Amount  from Table1  A
join Table2 B
on A.InvoiceNo =B.InvoiceNo 
Where A.date>=’20200201 00 :00 :00’ and A.date <=’20200201 00:00:00’
group by A.Customer

Answer 2

你似乎只想要窗口函数：

select customer_name, date, invoice_number,
count(*) over (partition by customer_name) as num_invoices,
sum(amount) over (partition by customer_name) as total_amount
from t
order by num_invoices desc,
customer_name,
date;

如果添加where子句，它将应用于所有列。

Answer 3

尝试以下查询。这将提供带有日期筛选的总发票和金额。

select A.customer,A.date,invoice_no,B.Total_Invoice,B.Total_Amount from test A join
(select customer,date,count(invoice_no) over (partition by customer) as Total_Invoice,
SUM(amount) over (partition by customer) as Total_Amount from test
where date>='20200101 00 :00 :00' and date <='20200301 00:00:00'
)B 
on A.date=B.date and A.customer=B.customer
order by customer;

输出：

C1  2020-01-01  1   2   900
C1  2020-03-01  5   2   900
C2  2020-01-01  2   2   800
C2  2020-02-01  4   2   800
C3  2020-02-01  3   1   200

如果您想要总发票(无日期过滤(和总金额(带日期过滤(，则可以使用以下查询。

select distinct A.customer,A.date,invoice_no,C.Total_Invoice,B.Total_Amount from test A join
(select customer,date,count(invoice_no) over (partition by customer) as Total_Invoice,
SUM(amount) over (partition by customer) as Total_Amount from test
where date>='20200101 00 :00 :00' and date <='20200301 00:00:00'
)B 
on A.date=B.date and A.customer=B.customer
join 
(select customer,COUNT(invoice_no) over (partition by customer) as Total_Invoice from test)C
on A.customer=C.customer
order by customer;

输出：

C1  2020-01-01  1   3   900
C1  2020-03-01  5   3   900
C2  2020-01-01  2   2   800
C2  2020-02-01  4   2   800
C3  2020-02-01  3   1   200