Hi all,
When updating our ticket history, if I want to delete our single ticket constituency simply based upon not having purchased tickets in the last year would this be the correct way to achieve this?
Cheers!
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4
IF @constituency is not null
delete a
from tx_const_cust a
where a.constituency = @constituency and
not exists (select * from lt_tkt_hist b
where a.customer_no = b.customer_no
and b.perf_dt > dateadd(year,-1,getdate()))
I have no idea where this came from
Ryan,
I'd set the end_dt in tx_const_cust to the current time instead of deleting the row.
-steve
something to consider but what I'm curious about in my lack of SQL skills is if this bit of code is the correct/best way to accomplish the minus 1 year logic.
b.perf_dt > dateadd(year,-1,getdate()))
Ah, sorry I mis-read.
It looks good by me, removing the constituency unless they have a performance within the last year.
I hope somebody will correct me if I'm wrong, but I think the "end date instead of deleting" might pose problems for any scripts you've got that automatically add the constituency if the person buys a ticket now, more than a year having passed. I thought it wasn't possible to have more than one of the same constituency per account, even if one version is inactive and the other starts after the previous ended.
You could use end_dt instead of deleting the row, and then alter the code that adds the constituency to check for it and just extend the end dt if the constituency already exists but getdate>end_dt... but it seems simpler to just delete it once it's "expired" and add it in fresh once they come back. I'm assuming now that the intention of this constituency is to imply "current" single ticket buyers, and not as a means for accurately representing the date range during which they are STBs. If I'm wrong on that front then nevermind.
I agree that deleting is probably the best policy, at least for what we have going currently and you are correct that you can't have two instances of the same constituency, it's been an enhancement request for awhile.
Thanks everyone!