0

How do I develop a tabular UDF that does not accept any values and calculates the commission for each employee_id. I have a table called called campaign offer

Create Table Campaign_Offer
(
Offer_id            integer identity(1,1) primary key,
Offer_date          Date        ,
Customer_id         int ,
Offer_value         money       ,
Product_name        varchar(20) ,
Acceptance_status   varchar(3)  ,
Employee_ID         char(10)    ,

Constraint CustomerID foreign key (Customer_id) references marketing_list(Id),
Constraint EmployeeID foreign key (Employee_ID) references Employee(Employee_id),
Constraint AcceptanceStatus Check (Acceptance_status in ('yes', 'no'))
)

This is the function I am trying to call

Create Function OfferCommission (@OfferValue money,@OfferDate date)
Returns money
As
Begin
RETURN @OfferValue * CHOOSE(DATEPART(QUARTER,@OfferDate), 0.1, 0.15, 0.2,    
0.25) 
END

I was trying it here

Create Function TabularCommission
(
@Offer_id           integer,
@Offer_value            money,
@Acceptance_status  varchar(3)  

)Returns table
As
Return
(
Select Employee_ID,dbo.OfferCommission(Offer_value, Offer_date) as  
Commission from Campaign_Offer
where Acceptance_status = 'yes'
group by Employee_ID
)
user2127184
  • 131
  • 1
  • 13

1 Answers1

0

You dont need to pass parameters to your outer function 

Create Function TabularCommission()
Returns table
As
Return
(
Select Employee_ID
      ,dbo.OfferCommission(Offer_value, Offer_date) as Commission 
from Campaign_Offer
where Acceptance_status = 'yes'
)
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • thanks but I'm getting Column 'Campaign_Offer.Offer_value because it is not contained in either an aggregate function or the GROUP BY clause. – user2127184 Sep 29 '15 at 20:23
  • You dont need `group by` here – M.Ali Sep 29 '15 at 20:58
  • If for some reason you do really want to have a GROUP BY then you need this. group by Employee_ID, dbo.OfferCommission(Offer_value, Offer_date) or you may just want a DISTINCT after the select. – Paul Spain Sep 29 '15 at 23:32