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
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