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 ------------!!!!!!!!!**********screwes 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
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

Open in new window

OddiCAsked:
Who is Participating?
 
YiogiConnect With a Mentor Commented:
It is groupping according to your clause actually. Since you are groupping by 9 fields it is natural that it will take all distinct combinations of those 9 fields. Anyway what I'd propose is to put this:
(select sum(bp.Qty_On_Order) where bp.Drop_Ship_Customer in (select DealerID from SalesChannels
where SalesRepID = SalesRepID))

in a udf so you don't have to include the dealer ID in your group by clause. So try changing that part above to:
dbo.udf_FindSum(sc.SalesRepID, dw.Cust_Name)

and create a udf to do what you need as in the snippet.
CREATE FUNCTION dbo.udf_FindSum(@SalesRepID BigInt, @CustomerName NVarChar(100))
RETURNS Decimal(18,4)
AS
 BEGIN 
   DECLARE @ReturnValue Decimal(18,4)
   SELECT @ReturnValue = SUM(bp.Qty_On_Order)
          FROM OEM_BOAT_PACKAGE bp
         INNER JOIN Cust_Name dw
               ON dw.one_system_cust_num = bp.Dealer_Number
               WHERE bp.Drop_Ship_Customer 
                     IN (select DealerID from SalesChannels
where SalesRepID = @SalesRepID)
                     AND dw.Cust_Name = @CustomerName
   RETURN @ReturnValue
 END

Open in new window

0
 
OddiCAuthor Commented:
Yiogi, thanks so far- I seem to be on the right track now but..... while executing your function for just one set of params takes under a second (and works)... when I put this function into my SP to get the aggregate 'Units Sold' numbers, it takes a looooooooong time to run (it has currently been running for 5+ minutes just now).

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(@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
         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',
0
 
OddiCAuthor Commented:
Here is the code i used to create the UDF, I mean of course. ; )
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
YiogiCommented:
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

Open in new window

0
 
OddiCAuthor Commented:
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!
0
 
OddiCAuthor Commented:
......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)!

0
 
OddiCAuthor Commented:
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(@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
         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_CUSTOMER] dw ON sc.OEMID = dw.one_system_cust_num
INNER JOIN [Replication].[dbo].[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, 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!


0
 
OddiCAuthor Commented:
(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.
0
All Courses

From novice to tech pro — start learning today.