Databases
StackOverflow2013
Postsdbo
POSQL Stored procedure to obtain top customers
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Stored procedure to obtain top customers
    primarykey
    data
    text
    <p>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).</p> <p>Here's some code:</p> <p>Table creation:</p> <pre><code>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 &gt; 0), Contributor int not null check (Contributor &gt; 0), Customer_number int not null check (Customer_number &gt; 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 ) </code></pre> <p>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.</p> <p>Here it is:</p> <pre><code>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 </code></pre> <p>Can someone help me get the two top customers?</p> <p>Thanks Chiapa</p> <p>PS: Here's some data to insert so you can test it better:</p> <pre><code>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) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USchiapa
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Stored procedure to obtain top customers
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Stored procedure to obtain top customers
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Stored procedure to obtain top customers
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload