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.
Huh, I would have used 'yy' instead of 'year', but checking it I find that 'year' works as well.
Handy way to check if you've got your date code working right is to try executing this query by itself:
select dateadd(year, -1, getdate())
If the date returned is a year ago today (and it is), all is well!