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
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
Thank you Dr. That's going straight into my boostnote file