Hi all, Just a not so humble brag.
For ages I've been looking to automate our gift vouchers and online forms away from CSIs to something a little more useful like an output set for automated sending by wordfly or an SSRS report where the form data is in distinct fields. Finally I've cracked the Stored Procedure pulling the data from TNEW to my Local table.
This will save my christmas morning not having to build and email gift vouchers. And we'll be able to take the war and peace sized answers that dance course students like to write about themselves that always blow up CSIs by going over the character limit.
I'll sleep well tonight
That's awesome, Heath! Congrats on such a big win :)
Thanks lovely human
Heath Wilder said:And we'll be able to take the war and peace sized answers that dance course students like to write about themselves that always blow up CSIs by going over the character limit.
New SQL Level Unlocked. Congratulations, and enjoy your SQL-parsed CSI goodness. *bows*
You can always use a query like this to pull the GC data.
declare @tblGC table(GcNo varchar(16),PaymentNo int,TXDate datetime,CustomerNo int,transaction_no int,PurchAmt money,RedemAmt money,SeqNo intprimary key (Paymentno, SeqNo))insert into @tblGC Select g.gc_no , p.payment_no, pmt_dt as TXDate , p.customer_no , transaction_no ,abs(iif( pmt_amt < 0, pmt_amt ,0)) as Purchase ,abs(iif( pmt_amt > 0, pmt_amt ,0)) as Redemption ,ROW_NUMBER () over (partition by p.Payment_no order by transaction_No) as SeqNofrom T_PAYMENT as P inner join T_GC as G on G.payment_no = p.payment_no Select * from(Select d.*,isnull((Select CustomerNo from @tblGC as G where g.SeqNo = d.MaxSeq and g.PaymentNo = d.PaymentNo and RedemAmt > 0 ),0) as LastUseCustomer ,PurchAmt - RedemAmt as Balance from (Select PaymentNo , GcNo,Max(iif(SeqNo = 1 and PurchAmt > 0,CustomerNo,0)) as Purchaser, Max(SeqNo) MaxSeq,Sum(PurchAmt) as PurchAmt , sum(RedemAmt) as RedemAmt,min(TXDate) as PurchaseDate, Max(TXDate) as LastTX,Max(IIf(RedemAmt > 0, TXDate, null)) as LastUsed from @tblGCgroup by PaymentNo , GcNo ) as D) as F--where Balance <> 0
Congrats Mr W, might have to 'borrow' that one. Feel like sharing?
Of course. I'll send it over
Output set to schedule the data to WF is in the future.
Thank you Dr. That's going straight into my boostnote file
I can help with that.
oooooh. I get to assign skill points!
It dumps the data in a local table so it should be fine. The only issue is the b'jillion other things on the plate at the moment as we launch all the shows on all the platforms.
This query is awesome; thanks Ronald!
The next logical step would be to figure out how to factor in Fiscal Year from TR_BATCH_PERIOD (I think that is where the FYs are defined) for the purchase date.
add the follwing to the end
left join TR_BATCH_PERIOD as PB on PurchaseDate between pb.start_dt and pb.end_dt left join TR_BATCH_PERIOD as LB on LastTX between lb.start_dt and lb.end_dt
If you want to go a little further. You can give them a page in TNEW to see the balance and apply them to an order or donation. So, force a login in the on email, see the fancy landing page, do something.., apply to the cart. Found this to be effective to encourage retaining revenue from previous season. ~80% kept using this approach.