none
SQL Stored Procedure RRS feed

  • 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


    Monday, July 9, 2018 6:30 AM

All replies

  • 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 :)

    Monday, July 9, 2018 5:44 AM
  • 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

    Monday, July 9, 2018 5:49 AM
  • Hi there, 

    Please find the database sample below:

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


    Monday, July 9, 2018 5:57 AM
  • 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

    Monday, July 9, 2018 6:02 AM
  • 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

    Monday, July 9, 2018 6:18 AM
  • 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

    Monday, July 9, 2018 6:23 AM
  • 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/

    Monday, July 9, 2018 8:23 AM