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


Tracing Merged Records in PE from old (merged) account to new account

--this function takes an int input of the possibly no-longer-existing account, and returns the account number (cltcode) of the current account



CREATE FUNCTION dbo.moa_merge_trace (
@old int
) RETURNS int
as begin
declare @new int, @count int
set @count = 0 -- to prevent infinite loops
--begin recursive trace
if exists (select cltcode from clients with(nolock) where cltcode = @old) return @old

while exists (SELECT FromID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713) and @count < 100
begin
set @new = (SELECT ToID FROM PATRONEDGE_MERGELOG with(nolock) where FromID = @old and metaobject_id = 713)
set @old = @new
set @count = @count + 1
end

return @new
end