CODEWITHSUNDEEP
sqlsql-serverstored-procedures
chiapa
chiapa

Reputation: 4412

SQL Stored procedure to obtain top customers

I'm trying to create a stored procedure that goes through a "SALES" table and returns the best two customers of a pharmacy (the two customers who have spent more money).

Here's some code:

Table creation:

create table Customer (
    Id_customer int identity(1,1) Primary Key,
    Name varchar(30),
    Address varchar(30),
    DOB datetime,
    ID_number int not null check (ID_number > 0),
    Contributor int not null check (Contributor > 0),
    Customer_number int not null check (Customer_number > 0)
    )

create table Sale (
    Id_sale int identity(1,1) Primary Key,
    Id_customer int not null references Customer(Id_customer),
    Sale_date datetime,
    total_without_tax money,
    total_with_tax money
    )

Well, I don't know if this is useful but I have a function that returns the total amount spent by a customer as long as I provide the customer's ID.

Here it is:

CREATE FUNCTION [dbo].[fGetTotalSpent]
(
    @Id_customer int
)
RETURNS money
AS
BEGIN
    declare @total money
    set @total = (select sum(total_with_tax) as 'Total Spent' from Sale where Id_customer=@Id_customer)
    return @total
END

Can someone help me get the two top customers?

Thanks Chiapa

PS: Here's some data to insert so you can test it better:

insert into customer values ('Jack', 'Big street', '1975.02.01', 123456789, 123456789, 2234567891)
insert into customer values ('Jim', 'Little street', '1985.02.01', 223456789, 223456789, 2234567891)
insert into customer values ('John', 'Large street', '1977.02.01', 323456789, 323456789, 3234567891)
insert into customer values ('Jenny', 'Huge street', '1979.02.01', 423456789, 423456789, 4234567891)

insert into sale values (1, '2013.04.30', null, 20)
insert into sale values (2, '2013.05.22', null, 10)
insert into sale values (3, '2013.03.29', null, 30)
insert into sale values (1, '2013.05.19', null, 34)
insert into sale values (1, '2013.06.04', null, 21)
insert into sale values (2, '2013.06.01', null, 10)
insert into sale values (2, '2013.05.08', null, 26)

Upvotes: 3

Views: 1900

Answers (3)

Chad Cook
Chad Cook

Reputation: 619

You can do this with a single query without any special functions:

select top 2 c.id_customer, c.name, sum(s.total_with_tax)
from customer c
join sale s on c.id_customer = s.id_customer
group by c.id_customer, c.name
order by sum(s.total_with_tax) desc

Upvotes: 5

Martin Smith
Martin Smith

Reputation: 453909

This joins onto a CTE with the top customers.

Remove the WITH TIES option if you want exactly 2 and don't want to include customers tied with the same spend.

WITH Top2
     AS (SELECT TOP 2 WITH TIES Id_customer,
                                SUM(total_with_tax) AS total_with_tax
         FROM   Sale
         GROUP  BY Id_customer
         ORDER  BY SUM(total_with_tax) DESC)
SELECT *
FROM   Customer C
       JOIN Top2 T
         ON C.Id_customer = T.Id_customer

Upvotes: 3

David Jashi
David Jashi

Reputation: 4511

I'm not really into SQL Server dialect, but this one will give you best customers in descending order along with money they spent:

select Id_customer, total_with_tax from
(select Id_customer, sum(total_with_tax) total_with_tax from Sale group by Id_customer)
order by total_with_tax desc

Upvotes: 2

Related Questions