Function to Concat Sales Rep Commission
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE FUNCTION [dbo].[ConcatSalesRepsCommission] | |
( | |
-- Add the parameters for the function here | |
@SalesRepID INT, @LocationID INT, @SurgeonID INT | |
) | |
/* | |
EXAMPLE: | |
SELECT dbo.ConcatSalesRepsCommission(14,45,36) | |
RETURNS: | |
0.05 (Category1), 0.05 (Category2), 0.05 (Category3) | |
*/ | |
RETURNS VARCHAR(8000) | |
AS | |
BEGIN | |
-- Declare the return variable here | |
DECLARE @Output VARCHAR(8000) | |
-- Add the T-SQL statements to compute the return value here | |
; | |
WITH x AS (SELECT CommissionPercent, | |
Categories = STUFF((SELECT ', ' + cat.Category | |
FROM dbo.Categories AS cat | |
INNER JOIN dbo.Commissions AS com ON cat.CategoryID = com.CategoryID | |
WHERE c.CommissionPercent = com.CommissionPercent | |
AND SalesRepID = @SalesRepID | |
AND SurgeonID = @SurgeonID | |
AND LocationID = @LocationID | |
FOR XML PATH , | |
TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') | |
FROM dbo.Commissions AS c), | |
y AS (SELECT s = RTRIM(CommissionPercent) + ' (' + Categories + ')' | |
FROM x | |
GROUP BY CommissionPercent, Categories) | |
SELECT @Output = STUFF((SELECT ', ' + s | |
FROM y | |
FOR XML PATH , | |
TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''); | |
-- Return the result of the function | |
RETURN @Output | |
END |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment