SQL Stored Procedure

Question

I have an existing business database with tables and was wondering if someone could help me create a stored procedure. 

The stored procedure which I would like creates needs to return the total sales value for a customer name based on the products ordered, quantity ordered and selling price. As well this will require a number of joins to get data from several tables, an expression to total the selling price, and a stored procedure that accepts an appropriate parameter. Return the stored procedure to return the total spend for a Mark Jacobi. The stored procedure should return the total spend and the name of the customer.

If someone could help me do this, it'll be greatly appreciated :) 

Please find the database sample below

https://www.dropbox.com/s/r1noqu1bczzcuou/17-18Sheffield%20Cycles1%281%29.mdb?dl=0

Answer 1

I have an existing business database with tables and was wondering if someone could help me create a stored procedure. 

The stored procedure which I would like creates needs to return the total sales value for a customer name based on the products ordered, quantity ordered and selling price. As well this will require a number of joins to get data from several tables, an expression to total the selling price, and a stored procedure that accepts an appropriate parameter. Return the stored procedure to return the total spend for a Mark Jacobi. The stored procedure should return the total spend and the name of the customer.

If someone could help me do this, it'll be greatly appreciated :)

Answer 2

Without knowing anything about your tables, its not possible for someone to help you out on this

You need to provide us the details of tables involved from where you need to columns with some sample data. Then explain what you need as output from the procedure for us to give a solution

As of now, as per your explanation only thing we can give is a stub like this

CREATE PROC GetCustomerDetails
@CustName varchar(100)
AS
SELECT c.Name,
SUM(od.Amount) AS TotalOrderValue,
SUM(od.Quantity) AS QtyOrdered,
COUNT(od.ProductID) AS ProductsOrdered,
...
FROM OrderDetails od
JOIN Customer c
ON c.CustomerID = od.CustomerID
WHERE c.Name = @CustName
GROUP BY c.Name

by asusming column names, tablenames etc

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 3

Hi there, 

Please find the database sample below:

www. dropbox.com/s/r1noqu1bczzcuou/17-18Sheffield%20 Cycles1%281%29.mdb?dl=0

Answer 4

Hi there, 

Please find the database sample below:

www. dropbox.com/s/r1noqu1bczzcuou/17-18Sheffield%20 Cycles1%281%29.mdb?dl=0

Sorry its not opening the page for me

Just post some sample data from the tables here (about 5 10 rows) with their structure

That should be enough for us

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 5

Hi there, 

In the last post I placed a space in the hyperlink between www. dropbox and the 20 cycles if you remove them the link should work

Answer 6

Hi there, 

In the last post I placed a space in the hyperlink between www. dropbox and the 20 cycles if you remove them the link should work

Its a mdb file

So are you on Access?

Then you should be posting this on Access forums

https://social.msdn.microsoft.com/Forums/Lync/en-US/home?forum=accessdev

This forum is for Transact SQL which is SQLServer's implementation of SQL

Alternatively if you're on sqlserver, post code for tables and data as CREATE TABLE.. INSERT statements rather than sending as files like this!

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 7

You could make (store) a query like:

SELECT tblCustomer.CustomerName, Sum([Quantity]*[SellingPrice]) AS Sales
FROM (tblCustomer INNER JOIN tblSOHeader ON tblCustomer.CustomerID = tblSOHeader.CustomerID) INNER JOIN (tblProduct INNER JOIN tblSOLine ON tblProduct.ProductID = tblSOLine.ProductID) ON tblSOHeader.SOHeaderID = tblSOLine.SOHeaderID
GROUP BY tblCustomer.CustomerName, tblCustomer.CustomerID
HAVING (((tblCustomer.CustomerID)=5));

---

Groeten, Peter http://access.xps350.com/