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)


SELECT

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

FROM

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

WHERE

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

ORDER BY

   o.nOrderId

 

 

 


Login to post a comment