September 18, 2009 at 3:57 pm
Here's the situation:
A salesperson visits a customer, and talks about 1 or more products. Studies have shown that the effectiveness of the call depends upon the order in which the products are discussed (on the order of 60% for the first product, 30% for the second, and 10% for the third). CRM systems allow a salesperson to record information about a call, and the products in "detail position" order.
What I want to do is place a dollar value on a call. But: Say the effectiveness is as above, and the salesperson only talks about a single product in the call. Then 100% of the value of the calltype should be assigned, not 60%. If the salesperson talks about only 2 products, then 60% of the calltype value is assigned to the first product, and 40% (30% + 10%) is assigned to the second product. In general, the pattern to be implemented is:
IF call detail position < number of details in the call
detail value = calltype value * detail position effectiveness percent
ELSE IF call detail position = number of details in the call
detail value = calltype value * (SUM of detail position effectiveness percent) from detail position to N
ELSE detail value = 0 (just for completeness)
In other words, the percentage to use for that middle value is the sum of the percentages, summed from the current detail position to the last detail position. Mathematically, let i be the detail position, n be the number of detail position effectiveness percentages: Sum the percentages, from i to n.
I have an implementation that involves a subquery to get that tricky detail value, but this sounds to me like a "reverse running total", so I wonder if anybody can figure out a better (ie, more set oriented) solution.
And, for extra credit ;-): There are some call types where the effectiveness percent is constant, regardless of detail position (a Seminar call type, for example, will have 100% for all detail positions). How would you modify your or my solution to handle that case?
[code="sql"]
CREATE TABLE #CallTypes (
type nvarchar(50),
value float
)
INSERT #CallTypes (type, value)
SELECT 'Breakfast Meeting', 100
UNION
SELECT 'Lunch Meeting', 200
UNION
SELECT 'Dinner Meeting', 250
CREATE TABLE #DetailPositionEffectiveness (
productid nvarchar(10),
detailposition int,
pct float
)
INSERT #DetailPositionEffectiveness (productid, detailposition, pct)
SELECT 'Alpha', 1, .6
UNION
SELECT 'Alpha', 2, .3
UNION
SELECT 'Alpha', 3, .1
UNION
SELECT 'Beta', 1, .6
UNION
SELECT 'Beta', 2, .25
UNION
SELECT 'Beta', 3, .1
UNION
SELECT 'Beta', 4, .05
UNION
SELECT 'X', 1, .6
UNION
SELECT 'X', 2, .4
CREATE TABLE #Calls (
callid int,
type nvarchar(50),
productid nvarchar(10),
detailposition int
)
INSERT #Calls (callid, type, productid, detailposition)
SELECT 1, 'Lunch Meeting', 'Alpha', 1
UNION
SELECT 1, 'Lunch Meeting', 'Beta', 2
UNION
SELECT 2, 'Dinner Meeting', 'Alpha', 1
UNION
SELECT 3, 'Breakfast Meeting', 'Beta', 1
UNION
SELECT 3, 'Breakfast Meeting', 'X', 2
UNION
SELECT 3, 'Breakfast Meeting', 'Alpha', 3
SELECT act.CallID, act.Type, act.ProductID, act.DetailPosition,
CASE WHEN act.NoDetails = act.DetailPosition THEN (SELECT SUM(CAST(dpct1.pct AS FLOAT))
FROM #DetailPositionEffectiveness dpct1
WHERE dpct1.ProductID = act.ProductID
AND dpct1.DetailPosition >= act.DetailPosition
GROUP BY dpct1.ProductID)
WHEN act.NoDetails > act.DetailPosition THEN dpct.pct
ELSE 0
END * act.Value AS AssignedValue
FROM #DetailPositionEffectiveness dpct
JOIN (
SELECT c.CallID,
c.Type,
c.ProductID,
c.DetailPosition,
(SELECT COUNT(*) FROM #Calls c1 WHERE c1.CallID = c.CallID) AS NoDetails,
ct.Value AS Value
FROM #Calls c
JOIN #CallTypes ct
ON c.Type = ct.Type
) act -- short for activity, fyi
ON dpct.ProductID = act.ProductID
AND dpct.DetailPosition = act.DetailPosition
DROP TABLE #DetailPositionEffectiveness
DROP TABLE #Calls
DROP TABLE #CallTypes
[/code]
Expected Results:
CallIDType ProductIDDetailPositionAssignedValue
1 Lunch Meeting Alpha1 120
1 Lunch Meeting Beta2 80
2 Dinner Meeting Alpha1 250
3 Breakfast MeetingAlpha3 10
3 Breakfast MeetingBeta1 60
3 Breakfast MeetingX 2 40
September 20, 2009 at 7:29 am
We all know SQL is not really great for running totals, but two things it does outstandingly well are summary queries and looking stuff up. Your percentage calculations could become a simple lookup if you just precalculated the values as shown below when you populate your #calltypes table.
In your query, first do a summary total of the max number of products discussed per call id, and join it to your detail on callID, followed by a join to the #calltypes table on meeting type, max products discussed, and detail position. No running total necessary.
CREATE TABLE #CallTypes (
type nvarchar(50),
maxProdDiscussed tinyInt,
detailPosition tinyint,
value float -- if you say so
)
INSERT #CallTypes (type, maxProdDiscussed,detailPosition,Value
SELECT 'Breakfast Meeting', 1,1,100 Union all
SELECT 'Breakfast Meeting', 2,1,60 Union all
SELECT 'Breakfast Meeting', 2,2,40 Union all
SELECT 'Breakfast Meeting', 3,1,60 Union all
SELECT 'Breakfast Meeting', 3,2,30 Union all
SELECT 'Breakfast Meeting', 3,3,10
select * from #callTypes
Or, if you prefer, have three separate value columns and just join on max products, then use a case statement to select the column based on the detail position.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply