Skip to content

Instantly share code, notes, and snippets.

@ajtatum
Created May 1, 2012 22:29
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save ajtatum/2571968 to your computer and use it in GitHub Desktop.
Function to Concat Sales Rep Commission
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