OddiC
asked on
Why does this SP not sum everything according to my GROUP BY clause?
Hello out there!
I have query in an SP that needs to sum all units for a particuliar 'SalesRep/OEM' combination. Everything works grreat until I try to create the summed up column. For whatever reason, it is returning more than one result for a 'SalesRep/OEM' combination if the combination is associated with more than one Dealer....
Here is the code I am using to try and get unique (only the sum of ALL the Rep and all OEM's "qty_on_order"- not broken out by "bp.Drop_Ship_Customer".
Many advance hanks to all who can offer help!!!!
Here is the code, I can answer any questions relatively fast:
ALTER PROCEDURE dbo.GetRepRankings
AS
BEGIN
select DISTINCT
sr.MailingName As 'Sales Rep',
dw.Cust_Name As 'OEM',
(select sum(bp.Qty_On_Order) where bp.Drop_Ship_Customer in (select DealerID from SalesChannels
where SalesRepID = SalesRepID)) As 'Units Sold', ------!!!!!!************** ********** * this is the part that ------------!!!!!!!!!***** *****screw es up the grouping....
dw.one_system_cust_num As 'OEM ID',
sr.ID As 'ID'
from SalesChannels sc
INNER JOIN SalesReps sr ON sc.SalesRepID = sr.ID
INNER JOIN DW_DIM_CUSTOMER dw ON sc.OEMID = dw.one_system_cust_num
INNER JOIN OEM_BOAT_PACKAGE bp ON sc.OEMID = bp.Dealer_Number
where dw.Channel_of_Distribution = 'IND BT CO/PANEL MFG/'
group by bp.Dealer_Number, dw.Cust_Name, sr.MailingName, sc.OEMID, sc.DealerID, sc.SalesRepID, dw.one_system_cust_num, sr.ID, bp.Drop_Ship_Customer --'Sales Rep'
order by 'Units Sold' DESC
END
GO
I have query in an SP that needs to sum all units for a particuliar 'SalesRep/OEM' combination. Everything works grreat until I try to create the summed up column. For whatever reason, it is returning more than one result for a 'SalesRep/OEM' combination if the combination is associated with more than one Dealer....
Here is the code I am using to try and get unique (only the sum of ALL the Rep and all OEM's "qty_on_order"- not broken out by "bp.Drop_Ship_Customer".
Many advance hanks to all who can offer help!!!!
Here is the code, I can answer any questions relatively fast:
ALTER PROCEDURE dbo.GetRepRankings
AS
BEGIN
select DISTINCT
sr.MailingName As 'Sales Rep',
dw.Cust_Name As 'OEM',
(select sum(bp.Qty_On_Order) where bp.Drop_Ship_Customer in (select DealerID from SalesChannels
where SalesRepID = SalesRepID)) As 'Units Sold', ------!!!!!!**************
dw.one_system_cust_num As 'OEM ID',
sr.ID As 'ID'
from SalesChannels sc
INNER JOIN SalesReps sr ON sc.SalesRepID = sr.ID
INNER JOIN DW_DIM_CUSTOMER dw ON sc.OEMID = dw.one_system_cust_num
INNER JOIN OEM_BOAT_PACKAGE bp ON sc.OEMID = bp.Dealer_Number
where dw.Channel_of_Distribution
group by bp.Dealer_Number, dw.Cust_Name, sr.MailingName, sc.OEMID, sc.DealerID, sc.SalesRepID, dw.one_system_cust_num, sr.ID, bp.Drop_Ship_Customer --'Sales Rep'
order by 'Units Sold' DESC
END
GO
ALTER PROCEDURE dbo.GetRepRankings
AS
BEGIN
select DISTINCT
sr.MailingName As 'Sales Rep',
dw.Cust_Name As 'OEM',
(select sum(bp.Qty_On_Order) where bp.Drop_Ship_Customer in (select DealerID from SalesChannels
where SalesRepID = SalesRepID))
As 'Units Sold',
dw.one_system_cust_num As 'OEM ID',
sr.ID As 'ID'
from SalesChannels sc
INNER JOIN SalesReps sr ON sc.SalesRepID = sr.ID
INNER JOIN DW_DIM_CUSTOMER dw ON sc.OEMID = dw.one_system_cust_num
INNER JOIN OEM_BOAT_PACKAGE bp ON sc.OEMID = bp.Dealer_Number
where dw.Channel_of_Distribution = 'IND BT CO/PANEL MFG/'
group by bp.Dealer_Number, dw.Cust_Name, sr.MailingName, sc.OEMID, sc.DealerID, sc.SalesRepID, dw.one_system_cust_num, sr.ID, bp.Drop_Ship_Customer --'Sales Rep'
order by 'Units Sold' DESC
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is the code i used to create the UDF, I mean of course. ; )
You can speed it up yes but don't forget that the function has to be executed once for every single row in your end results. Anyway you can speed it up by making sure you have appropriate indices to all your tables firstly and secondly try the tweak of no locks in table selects both in your function and your original select. So your function would be as in the snippet. You can do the same to the original query, its only 2 extra words at the end of your selects and joins.
ALTER FUNCTION dbo.udf_FindSum(@SalesRepID Int, @OEMID NVarChar(9))
RETURNS Int
AS
BEGIN
DECLARE @ReturnValue Int
SELECT @ReturnValue = SUM(bp.Qty_On_Order)
FROM [Replication].[dbo].[OEM_BOAT_PACKAGE] bp WITH (NOLOCK)
INNER JOIN SalesChannels sc WITH (NOLOCK)
ON bp.Dealer_Number = sc.DealerID
--ON dw.one_system_cust_num = bp.Dealer_Number
WHERE bp.Drop_Ship_Customer
IN (select DealerID from SalesChannels
where SalesRepID = @SalesRepID)
AND bp.Dealer_Number = @OEMID
RETURN @ReturnValue
END
ASKER
I have verified that all the tables involved are properly indexed for the columns being searched. I also added the WITH (NO LOCK) and yet still this query will not execute in under several minutes (it is currently at over 4 minutes and counting). There are several (1000's) of records I am searching through in almost all of these tables, but, there has to be an easier (non-function-based) way to do this.
The slowness of this reminds me of when I (naively) was trynig to use a CURSOR in an SP a few years back- that also, has to operate row-by-row, and is way slow.
Isn't there just a set based way to get the info I need, grouped only by "bp.Dealer_Number" (which is OEM and different than "bp.Drop_Ship_Customer"- "bp.Drop_Ship_Customer" is what I want to eliminate from the grouping.
I appreciate all of your help. I just wish I could make this work! lol. I swear I have worked on much harder queries before... I think I'll get it soon. Any and all suggestion is most welcome!
Thanks again in advance!
The slowness of this reminds me of when I (naively) was trynig to use a CURSOR in an SP a few years back- that also, has to operate row-by-row, and is way slow.
Isn't there just a set based way to get the info I need, grouped only by "bp.Dealer_Number" (which is OEM and different than "bp.Drop_Ship_Customer"- "bp.Drop_Ship_Customer" is what I want to eliminate from the grouping.
I appreciate all of your help. I just wish I could make this work! lol. I swear I have worked on much harder queries before... I think I'll get it soon. Any and all suggestion is most welcome!
Thanks again in advance!
ASKER
......it seems i'm on to SOMEthing.. I removed the "DISTINCT" keywork from the top of the original SP and now the SP runs in less than one seceond (WITH the function) however, I am getting NULL for all my totals...
I will keep checking, and figure it out (or at least have some better info to provide)!
I will keep checking, and figure it out (or at least have some better info to provide)!
ASKER
GOT IT! (i was joining an incorrect column in the UDF). If you are curious, here is the code that works (in about .02 seconds!):
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION dbo.udf_FindSum(@SalesRepI D Int, @OEMID NVarChar(9))
RETURNS Int
AS
BEGIN
DECLARE @ReturnValue Int
SELECT @ReturnValue = SUM(bp.Qty_On_Order)
FROM [Replication].[dbo].[OEM_B OAT_PACKAG E] bp
INNER JOIN SalesChannels sc
ON bp.Dealer_Number = sc.OEMID
WHERE bp.Drop_Ship_Customer
IN (select DealerID from SalesChannels
where SalesRepID = @SalesRepID)
RETURN @ReturnValue
END
---------------and the Stored Proc---------------------- ---------- ---------- ---------- ---------
ALTER PROCEDURE [dbo].[GetRepRankings]
AS
BEGIN
select
sr.MailingName As 'Sales Rep',
dw.Cust_Name As 'OEM',
(select dbo.udf_FindSum(sr.ID, sc.OEMID)) As 'Units Sold',
dw.one_system_cust_num As 'OEM ID',
sr.ID As 'ID'
from SalesChannels sc
INNER JOIN SalesReps sr ON sc.SalesRepID = sr.ID
INNER JOIN [Replication].[dbo].[DIM_C USTOMER] dw ON sc.OEMID = dw.one_system_cust_num
INNER JOIN [Replication].[dbo].[OEM_B OAT_PACKAG E] bp ON sc.OEMID = bp.Dealer_Number
where dw.Channel_of_Distribution = 'IND BT CO/PANEL MFG/'
group by bp.Dealer_Number, dw.Cust_Name, sr.MailingName, sc.OEMID, dw.one_system_cust_num, sr.ID, sc.SalesRepID
order by 'Units Sold' DESC
END
THANK YOU again very much for your help- without your suggestion of using a UDF to overcome the grouping issue I would have been utterly lost.
Peace Yiogi!
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION dbo.udf_FindSum(@SalesRepI
RETURNS Int
AS
BEGIN
DECLARE @ReturnValue Int
SELECT @ReturnValue = SUM(bp.Qty_On_Order)
FROM [Replication].[dbo].[OEM_B
INNER JOIN SalesChannels sc
ON bp.Dealer_Number = sc.OEMID
WHERE bp.Drop_Ship_Customer
IN (select DealerID from SalesChannels
where SalesRepID = @SalesRepID)
RETURN @ReturnValue
END
---------------and the Stored Proc----------------------
ALTER PROCEDURE [dbo].[GetRepRankings]
AS
BEGIN
select
sr.MailingName As 'Sales Rep',
dw.Cust_Name As 'OEM',
(select dbo.udf_FindSum(sr.ID, sc.OEMID)) As 'Units Sold',
dw.one_system_cust_num As 'OEM ID',
sr.ID As 'ID'
from SalesChannels sc
INNER JOIN SalesReps sr ON sc.SalesRepID = sr.ID
INNER JOIN [Replication].[dbo].[DIM_C
INNER JOIN [Replication].[dbo].[OEM_B
where dw.Channel_of_Distribution
group by bp.Dealer_Number, dw.Cust_Name, sr.MailingName, sc.OEMID, dw.one_system_cust_num, sr.ID, sc.SalesRepID
order by 'Units Sold' DESC
END
THANK YOU again very much for your help- without your suggestion of using a UDF to overcome the grouping issue I would have been utterly lost.
Peace Yiogi!
ASKER
(the naming conventions with the data I'm working with caused more confusion than necessary on this one! (ie. OEMID links to "Dealer_Number" and "DealerID links to "Drop_Ship_Customer") wierd, I know.... but some things you just can't change (without breaking all sorts of legacy stuff!) lol.
ASKER
Is there any way I can speed it up? Here is the code i used to create the query and get the "Units Sold" column.
ALTER FUNCTION dbo.udf_FindSum(@SalesRepI
RETURNS Int
AS
BEGIN
DECLARE @ReturnValue Int
SELECT @ReturnValue = SUM(bp.Qty_On_Order)
FROM [Replication].[dbo].[OEM_B
INNER JOIN SalesChannels sc ON bp.Dealer_Number = sc.DealerID
--ON dw.one_system_cust_num = bp.Dealer_Number
WHERE bp.Drop_Ship_Customer
IN (select DealerID from SalesChannels
where SalesRepID = @SalesRepID)
AND bp.Dealer_Number = @OEMID
RETURN @ReturnValue
END
(select dbo.udf_FindSum(sr.ID, sc.OEMID)) As 'Units Sold',