CODEWITHSUNDEEP
sqlsql-server
Angel
Angel

Reputation: 83

Need to calculate count of ticket for each customer on a selected date alone with daily sales on quantity and total

I need to calculate the number of ticket for selected time period, and this number of ticket should be per customer. Below is the table , in which I have line level data.

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

My output should be like below

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

I can get the expected output from my below sql query

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

And the problem is I will select FROM DATE and TO DATE in the filter.And my filter is inside A ,So the Total Invoice value in B is not respecting the selected date. Please advice me how to use one filter for date and that should be for both A and B

Upvotes: 0

Views: 501

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

You seem to just want window functions:

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;

If you add a where clause, it will apply to all columns.

Upvotes: 1

Naveen Kumar
Naveen Kumar

Reputation: 2006

Try the below query. This will give the total invoice and amount with date filtration. 

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;

Output:

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

And if you want total invoice(without date filtration) and total amount (with date filtration) then you can use the following query.

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;

Output:

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

Upvotes: 0

Deepak Kumar
Deepak Kumar

Reputation: 308

Try below and let us know, only thing here is you wont get 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

Upvotes: 1

Related Questions