July 8, 2018 at 11:38 pm
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
July 8, 2018 at 11:51 pm
Nqobilemoyo - Sunday, July 8, 2018 11:38 PMI 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
I strongly suggest that you post the DDL (create table) scripts, sample data as an insert statement and the expected results, very few if any are willing to download and work on a data sample in an Access database!
😎
What data platform do you intend to run this stored procedure on?
July 9, 2018 at 12:15 am
Hi there, sorry about this but I am very new to sql and have minimal experience.I was wondering if someone like yourself could aid me with this query. I have quickly created a DDL however i am unsure if its correct.Please find it below:
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
July 9, 2018 at 1:35 am
Hi,
I hope this will be of help for you:
IF EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GetCustomerDetails'
AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE dbo.GetCustomerDetails
GO
-- Usage: dbo.GetCustomerDetails 'CustomerName'
CREATE OR ALTER PROCEDURE dbo.GetCustomerDetails
@CustomerName NVARCHAR(510)
AS
BEGIN
SELECT
C.CustomerName
,SUM(SOL.Quantity) AS TotalQuantity
,SUM(SOL.Quantity*P.SellingPrice) AS SalesTotal
,COUNT(SOL.ProductID) AS ProductCount
FROM tblCustomer C WITH(NOLOCK)
LEFT JOIN tblSOHeader SOH WITH(NOLOCK) ON C.CustomerID = SOH.CustomerID
LEFT JOIN tblSOLine SOL WITH(NOLOCK) ON SOH.SOHeaderID = SOL.SOHeaderID
LEFT JOIN tblProduct P WITH(NOLOCK) ON SOL.ProductID = P.ProductID
WHERE C.CustomerName = @CustomerName
GROUP BY C.CustomerName
END
But, I think you wont be able to get the VAT details as the VAT Id in tblSOLine is not mapped properly. Please let me know.
July 9, 2018 at 1:49 am
Thank you! Do you mind if you could comment or explain to me what the code does and how it works?
July 9, 2018 at 1:58 am
Sure!
Basically, I am taking Customer table as the base table here and then gradually I am drilling down to the details about the order details. In that process, I can see tblSOHeader is the table that is linked to tblCustomer. So, first I put a LEFT JOIN with this table (LEFT because I don't want to remove those Customers from my list who has not placed any orders, it will show NULL for TotalQuantity, ProductCount and SalesTotal for such cases though). Next, I am putting tblSOLine because it has direct relation with tblSOHeader. Next comes tblProduct as each line item in tblSOLine has a ProductId that maps to an entry in this table. I needed to include tblProduct since you do not have price in tblSOLine table, so I am picking it from tblProduct.
Please let me know if this helps!!
Thanks.
July 9, 2018 at 2:01 am
Thank you so soo much! 🙂 you've been great help
July 9, 2018 at 2:05 am
debasis.yours - Monday, July 9, 2018 1:35 AMHi,I hope this will be of help for you:
IF EXISTS(SELECT NULL FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GetCustomerDetails'
AND ROUTINE_TYPE = 'PROCEDURE')
DROP PROCEDURE dbo.GetCustomerDetails
GO-- Usage: dbo.GetCustomerDetails 'CustomerName'
CREATE OR ALTER PROCEDURE dbo.GetCustomerDetails
@CustomerName NVARCHAR(510)
AS
BEGIN
SELECT
C.CustomerName
,SUM(SOL.Quantity) AS TotalQuantity
,SUM(SOL.Quantity*P.SellingPrice) AS SalesTotal
,COUNT(SOL.ProductID) AS ProductCount
FROM tblCustomer C WITH(NOLOCK)
LEFT JOIN tblSOHeader SOH WITH(NOLOCK) ON C.CustomerID = SOH.CustomerID
LEFT JOIN tblSOLine SOL WITH(NOLOCK) ON SOH.SOHeaderID = SOL.SOHeaderID
LEFT JOIN tblProduct P WITH(NOLOCK) ON SOL.ProductID = P.ProductID
WHERE C.CustomerName = @CustomerName
GROUP BY C.CustomerName
END
But, I think you wont be able to get the VAT details as the VAT Id in tblSOLine is not mapped properly. Please let me know.
Why are you suggesting using the NOLOCK hint, don't you realize that this can lead to incorrect results?
😎
July 9, 2018 at 2:11 am
Thanks Eirikur for pointing that out.
We can remove the WITH(NOLOCK) hint if execution speed is not a concern. This will give a consistent result.
July 9, 2018 at 2:15 am
Hi there again sorry to be a pain but do you think you could break down your code explanation even further i really want to get a grip of it as i am new to everything
July 9, 2018 at 2:20 am
Yes, actually WITH(NOLOCK) hint may return incorrect result sometimes by showing data from other uncommitted transactions (possibly by other users). So, to get a consistent view of the current data, please remove the WITH(NOLOCK) hints from the stored procedure query.
Thanks.
July 9, 2018 at 2:58 am
debasis.yours - Monday, July 9, 2018 2:20 AMYes, actually WITH(NOLOCK) hint may return incorrect result sometimes by showing data from other uncommitted transactions (possibly by other users).
That really is the least of its problems. There are way more correctness concerns with Nolock than just dirty reads. You really shouldn't be recommending it or using it (and it is NOT a case that you must use Nolock to get good performance)
You've also used SQL Server 2017 specific code in a SQL 2016 forum. (and dropping the procedure before doing a CREATE OR ALTER is an odd choice, the whole point of CREATE OR ALTER is that you don't need to drop the procedure or check for existence first)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2018 at 10:13 am
debasis.yours - Monday, July 9, 2018 2:20 AMYes, actually WITH(NOLOCK) hint may return incorrect result sometimes by showing data from other uncommitted transactions (possibly by other users). So, to get a consistent view of the current data, please remove the WITH(NOLOCK) hints from the stored procedure query.Thanks.
How can I check that the results of the sql code you have create work? What code do I need to execute to view it?
July 9, 2018 at 10:23 am
EXEC dbo.GetCustomerDetails @CustomerName = 'Customer name here'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2018 at 10:35 am
GilaMonster - Monday, July 9, 2018 2:58 AMdebasis.yours - Monday, July 9, 2018 2:20 AMYes, actually WITH(NOLOCK) hint may return incorrect result sometimes by showing data from other uncommitted transactions (possibly by other users).That really is the least of its problems. There are way more correctness concerns with Nolock than just dirty reads. You really shouldn't be recommending it or using it (and it is NOT a case that you must use Nolock to get good performance)
You've also used SQL Server 2017 specific code in a SQL 2016 forum. (and dropping the procedure before doing a CREATE OR ALTER is an odd choice, the whole point of CREATE OR ALTER is that you don't need to drop the procedure or check for existence first)
I thought the correctness issue with NOLOCK was dirty reads, because nonrepeatable reads and phantom reads can also occur with normal default isolation level of READ COMMITTED. That does not mean that dirty reads are a good thing, but you have to look at the chance of that actually occurring and not just automatically throw out NOLOCK. For historical data that does not get UPDATEd or DELETEd, I don't see any advantage to using READ COMMITTED. (I know, I'm a heretic as usual.)
Edit: Also, many people to assume that you didn't use NOLOCK the results will be "pure". Not necessarily. In many cases, NOLOCK and RC would have the same data issues, if an issue was going to arise for that query.
That said, I do have some database at snapshot isolation level (despite the overhead of it) to, yes, avoid having to use NOLOCK and still gain concurrency. But that is truly active, "live" data, that does get updated and deleted frequently.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply