Calculate the actual price of a subscription ticket

PE does not do a good job at telling you the actual ticket value for subscription tickets... The subscription may have a 20% discount, yet the reports would report full value. Use this function as such: select moa_subtik_actual_price(tiscode) from ticketssubscription .... to get the actual value. Enjoy ;-)


CREATE FUNCTION dbo.moa_seriestik_actual_price (@prorecnum int)
RETURNS money AS
--added 11/15/06 jrh
-----------------------------------------------WILL RETURN NEGATIVE VALUE FOR RETURNS-------
--for reports that need live data, and only process a handful of tix

BEGIN

--declare @prorecnum int
--set @prorecnum = 224458
declare @actualprice money
set @actualprice = (
SELECT top 1 (subsprogram.proprice * procach.m) + isnull(discounts.clcsum,0) AS actualprice

FROM (SELECT cc.clcTicketNum, SUM(cc.clcSum * cach.m) AS clcsum
FROM CalculatedCommission cc WITH (NOLOCK) INNER JOIN
Commission WITH (NOLOCK) ON cc.clcCommission = Commission.cmsCode
inner join (select 1 as c, -1 as m union select 0 , 1) as cach on cc.clccachinout = cach.c
WHERE (Commission.cmsCommissionType = 4)
GROUP BY clcticketnum)
discounts right outer JOIN
subsprogram WITH (NOLOCK) ON discounts.clcTicketNum = subsprogram.prorecnum
inner join
(select 10 as s, 1 as m union
select 17, 1 union
select 28, 1 union
select 18, 1 union
select 29, -1 union
select 27, -1 union
select 21, - 1
) as procach on subsprogram.procurrentstatus = procach.s
WHERE (subsprogram.prorecnum = @prorecnum)
)

return isnull(@actualprice,0)

END