Sample interest weighting procedure

I too must confess that I just use UPDATE existing, DELETE and INSERT new. I haven't applied it to interest weighting but have a job setup for updating/adding ranking

I'd advise against using seated paid as a standard for updating given the ticket record can be removed from the order. If the money is allocated to a different ticket then the constituent record will have overstated weights.

How do you handle returns for type 12 given the wide range of reasons tickets get returned? For example a ticket may get returned to be upgraded to a membership which means the sale should still count. However if it gets returned because the customer changed their mind about the product that should not count.

Lastly I'd want to have an audit table to know exactly whet values were added (or removed) and when. I'd then use the audit table to update the values on the attribute on the constituent record so that it would be visible in the application (although my preference would be to just have them pull the weight data from a report since it is mostly used for analytics anyway). The would then allow the removal of a record by either changing its status of inserting a record that would decrement the count if he ticket was returned.

Hi Heath, sorry for the delay in responding.  It's been hectic over here.  I appreciate your helping to take a look when you get around to it.  It's a big help and unfortunately, I am only a basic SQL user and don't have knowledge on writing code, just simply applying it.  (le sigh)

Hi Dan, not sure if solved this one but if you're basing your script on the in Sadie's post above you should just be able to replace the following section:

--insert new rows for any which are not in the table
insert into tx_cust_tkw
select t.customer_no, t.tkw, 'N', t.weight, substring(@@servername,1,16),'PROCESS', getdate(), null, null
from #tkw t
where tkw_update = 'N'

with this:

--insert new rows for any which are not in the table
insert into tx_cust_tkw (customer_no, tkw, selected, weight)
select t.customer_no, t.tkw, 'N', t.weight
from #tkw t
where tkw_update = 'N'

Hi Thomas, so sorry for the long delay in responding.  Thank you so much for sending over that new code...it worked!!  Eureka!!

Thomas, I was able to get this procedure to work with your help, but I am struggling again with the object #tkw already being in the database. You mentioned doing an UPDATE existing, DELETE and INSERT new query.  Would you be able to help me format that properly for this #tkw "temp table"? It would be a HUGE help.

As a general rule, when I am using temporary tables ("#table_name") I make a point to delete any table by that name before creating it, e.g.

IF OBJECT_ID('tempdb..#temp_promo_order', 'U') IS NOT NULL
    DROP TABLE #temp_promo_order
GO