Gift Card Query

In response to a discussion on a thread about gift card reporting I engaged in a bit of intellectual curiosity and wrote a query to display gift card data knowing I would need to write one for the aquarium at some point in the future based on the limitations of the canned reports. That bill has now come due and below is a more polished (but not finalized) version of the same query as driven by the needs of our organization. If Tessitura ever enables gift card recharging functionality it will support that as well with no changes needed.

So far we only have 2.9K gift cards in the system, but have over 1.8 million payment records and the query runs in under a second for all the different options.

Here is a sample of the results:

Here is the query (requires SQL Server 2012 or newer):

Declare
@StartTXDate        date  = '12/2/2020'
,@EndTXDate            datetime  = '12/30/2020'
,@ResultType        tinyint    = 0

--0 = All
--1 = GC with Balance
--2 = Unused
--3 = Partial Use
--4 = No Balance
--97 = Historical
--98 = Use Date
--99 = New Cards

Declare
@rStartTXDate        date  
,@rEndTXDate        datetime
,@rResultType        tinyint    

set @rStartTXDate   = isnull(@StartTXDate , '01/01/1900')
Set @rEndTXDate = dbo.lfn_MBA_EOD(isnull(@EndTXDate , dateadd(Day,-1,Getdate())))
Set @rResultType = ISNULL (@ResultType,1)

Set ArithAbort On
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

declare @tblGC table(
GcNo            varchar(16)
,PaymentNo        int INDEX idxPaymentNo CLUSTERED
,TXDate            datetime INDEX idxTXDate NONCLUSTERED
,CustomerNo        int
,transaction_no    int
,PurchAmt        money
,RedemAmt        money
,SeqNo            int
primary 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 SeqNo
from T_PAYMENT as P
inner  join T_GC as G on G.payment_no = p.payment_no
where (p.pmt_dt <= @rEndTXDate  and @rResultType >= 90)
or @rResultType < 90

Select f.*
,isnull( Stuff((Select ' ~ ' +
    FORMAT( TXDate, 'G', 'en-US' ) + ' - ' + FORMAT( PurchAmt, 'C2', 'en-US' )
    from @tblGC   where PaymentNo = f.PaymentNo and PurchAmt > 0   
    order by SeqNo FOR XML PATH('')), 1, 3,''),'' ) as ChargeHistory
,isnull( Stuff((Select ' ~ ' +
    FORMAT( TXDate, 'G', 'en-US' ) + ' - ' + FORMAT( RedemAmt, 'C2', 'en-US' )
    from @tblGC   where PaymentNo = f.PaymentNo and RedemAmt > 0   
    order by SeqNo   FOR XML PATH('')), 1, 3,''),'' ) as UsageHistory
, isnull(pc.fname,'') as PurchFirstName , isnull(pc.lname,'') as PurchLastName
, isnull(LC.fname,'') as LastUseFirstName , isnull(Lc.lname,'') as LastUseLastName

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
    ,Max(iif(SeqNo = 1 and PurchAmt > 0,purchAmt,0)) as OriginalAmount
    ,Sum(PurchAmt) as PurchAmt , sum(RedemAmt) as RedemAmt
    ,min(iif(SeqNo = 1,TXDate,null)) as PurchaseDate, Max(TXDate) as LastTX
    ,Sum(iif( RedemAmt > 0,1,0)) as UseCount
    ,Max(IIf(RedemAmt > 0, TXDate, null)) as LastUsed
     from @tblGC
    group by PaymentNo , GcNo ) as D) as F
left join T_CUSTOMER as PC on f.Purchaser = pc.customer_no
left join T_CUSTOMER as LC on f.LastUseCustomer  = lc.customer_no
Where
    (@rResultType = 0)
    or (@rResultType = 1 and Balance <> 0)
    or (@rResultType = 2 and RedemAmt = 0)
    or (@rResultType = 3 and Balance !=0 and RedemAmt > 0)
    or (@rResultType = 4 and Balance = 0)
    or (@rResultType = 97 and LastTX  <= @rEndTXDate )
    or (@rResultType = 98 and LastUsed  between @rStartTXDate  and @rEndTXDate )
    or (@rResultType = 99 and PurchaseDate   between @rStartTXDate  and @rEndTXDate )
order by PurchaseDate , GcNo

The function lfn_MBA_EOD just calculates the end of day to save me from having to repeat the same logic over and over again:

IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[LFN_MBA_EOD]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
drop function [dbo].[LFN_MBA_EOD]

Go
CREATE FUNCTION [dbo].[LFN_MBA_EOD](@DateValue datetime)
RETURNS datetime
AS
BEGIN

  RETURN dateadd(ms,-3,cast(dateadd(day,1,cast(isnull(@DateValue,getdate()) as date)) as datetime))
END