help with custom script for refunds

Can anyone help with this script.  It works fine as a script where I manually change the dates each month on line 28 but I want to have the dates in 'parameters' to make it more user friendly for others.

I think I've figured out how to do the parameters for a start and end date but I don't know how to change the script.

It needs to show refunds on processed orders only, refunded during the dates set in the parameters, nothing cancelled.

(there are scripts on the system but they don't exclude cancelled orders)


o.nOrderId AS 'Order ID',

o.Source AS 'Source',

o.SubSource AS 'Sub-Source',

o.fTotalCharge AS 'Order Total',

oi.Taxrate AS 'VATRate',

o.dReceievedDate AS 'Order Date',

orf.CreateDate AS 'Refund Creation Date',

o.cFullName AS 'Customer Name',

oi.CostIncTax AS 'Item Cost',

o.cCurrency AS 'Currency',

c.cCountry AS 'Country',

 O.cPostCode AS 'PostCode',

c.TaxRate AS 'CountryTaxRate',

o.dProcessedOn AS 'ProcessedDate',

orf.Amount AS 'Refund Cost',

orf.Actioned AS 'Refund Actioned',

orf.Reason AS 'Refund Note (Reason)'


Order_Refund orf

INNER JOIN [order] o on orf.fkOrderId =o.pkorderid

LEFT OUTER join [OrderItem] AS oi ON o.pkOrderID = oi.fkOrderID and orf.fkOrderItemId =oi.rowid

INNER JOIN ListCountries c on o.fkCountryId = c.pkCountryId


   o.bProcessed = 1

   AND (orf.CreateDate BETWEEN '06/01/2021' AND '07/01/2021')

   AND (o.dProcessedOn BETWEEN '01/01/2013' AND '01/31/2099')






Hey, if you still need help on this - 

under WHERE you can try including "orf.CreateDate between @StartDate and @EndDate" which will link to the parameters

Also "o.dProcessedOn IS NOT NULL" should ignore the orders that have been cancelled from open order.

Then you can add the 2 parameters with Query Paramater Name StartDate and EndDate, whatever Display Name you want for them and the display type needs to be date.

Thank you so much!  Appreciated

Login to post a comment