Orders - Refund Totals by Refund Timestamp VAT Free
DECLARE @Startdate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2016-06-12 00:00'
SET @EndDate = '2016-06-18 23:59:59'
DECLARE @defaultUKVATRate DECIMAL(18,4)
SET @defaultUKVATRate = (
SELECT
Rate
FROM
VATRates
WHERE
ShippingCountryID = 1903
AND CategoryID = 0
)
SELECT DISTINCT
Orders.OrderID,
PaymentProcessorTransactions.[Timestamp] AS [RefundTimestamp],
'Card' AS PaymentType,
SUM(PaymentProcessorTransactions.Amount) AS [RefundTotal],
CASE
WHEN Orders.OrderID IN (
SELECT DISTINCT
OrderItems.OrderID
FROM
OrderItems
INNER JOIN Stock
ON Stock.StockID = OrderItems.StockID
INNER JOIN Models
ON Models.ModelID = Stock.ModelID
INNER JOIN Categories
ON Categories.CategoryID = Models.CategoryID
INNER JOIN VATRates
ON VATRates.CategoryID = Categories.CategoryID
AND VATRates.ShippingCountryID = 1903
WHERE
VATRates.Rate <> @defaultUKVATRate
)
THEN 'True'
ELSE 'False'
END AS [HasVATFreeItems]
FROM
Orders
INNER JOIN
PaymentProcessorTransactions
ON PaymentProcessorTransactions.OrderID = Orders.OrderID
WHERE
Orders.IsPayPal = 0
AND
Orders.VAT = 0
AND
PaymentProcessorTransactions.[Timestamp] BETWEEN @StartDate AND @EndDate
AND
PaymentProcessorTransactions.TransactionType = 5 --rebate
AND
PaymentProcessorTransactions.successful = 1
GROUP BY
PaymentProcessorTransactions.[Timestamp],
Orders.OrderID
UNION
SELECT DISTINCT
Orders.OrderID,
PayPalTransactions.[Timestamp] AS [RefundTimestamp],
'PayPal' AS PaymentType,
SUM(PayPalTransactions.Amount) AS [RefundTotal],
CASE
WHEN Orders.OrderID IN (
SELECT DISTINCT
OrderItems.OrderID
FROM
OrderItems
INNER JOIN Stock
ON Stock.StockID = OrderItems.StockID
INNER JOIN Models
ON Models.ModelID = Stock.ModelID
INNER JOIN Categories
ON Categories.CategoryID = Models.CategoryID
INNER JOIN VATRates
ON VATRates.CategoryID = Categories.CategoryID
AND VATRates.ShippingCountryID = 1903
WHERE
VATRates.Rate <> @defaultUKVATRate
)
THEN 'True'
ELSE 'False'
END AS [HasVATFreeItems]
FROM
Orders
INNER JOIN
PayPalTransactions
ON PayPalTransactions.OrderID = Orders.OrderID
WHERE
Orders.IsPayPal = 1
AND
Orders.VAT = 0
AND
PayPalTransactions.[Timestamp] BETWEEN @StartDate AND @EndDate
AND
PayPalTransactions.Method = 5 --rebate
AND
PayPalTransactions.successful = 1
GROUP BY
PayPalTransactions.[Timestamp],
Orders.OrderID
Orders - Refund Totals over Date Range
DECLARE @Startdate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2014-02-01 00:00:00'
SET @EndDate = '2015-01-31 23:59:59'
DECLARE @defaultUKVATRate DECIMAL(18,4)
SET @defaultUKVATRate = (
SELECT
Rate
FROM
VATRates
WHERE
ShippingCountryID = 1903
AND CategoryID = 0
)
SELECT DISTINCT
Orders.OrderID,
Orders.[Timestamp] AS [OrderTimestamp],
PaymentProcessorTransactions.[Timestamp] AS [RefundTimestamp],
'Card' AS PaymentType,
SUM(PaymentProcessorTransactions.Amount) AS [RefundTotal],
(SUM(PaymentProcessorTransactions.Amount) - (SUM(PaymentProcessorTransactions.Amount) / 1.2)) AS [RefundVatAmount],
CASE
WHEN Orders.OrderID IN (
SELECT DISTINCT
OrderItems.OrderID
FROM
OrderItems
INNER JOIN Stock
ON Stock.StockID = OrderItems.StockID
INNER JOIN Models
ON Models.ModelID = Stock.ModelID
INNER JOIN Categories
ON Categories.CategoryID = Models.CategoryID
INNER JOIN VATRates
ON VATRates.CategoryID = Categories.CategoryID
AND VATRates.ShippingCountryID = 1903
WHERE
VATRates.Rate <> @defaultUKVATRate
)
THEN 'True'
ELSE 'False'
END AS [HasVATFreeItems]
FROM
Orders
INNER JOIN
PaymentProcessorTransactions
ON PaymentProcessorTransactions.OrderID = Orders.OrderID
WHERE
Orders.IsPayPal = 0
AND
Orders.[timestamp] BETWEEN @StartDate AND @EndDate
AND
PaymentProcessorTransactions.TransactionType = 5 --rebate
AND
PaymentProcessorTransactions.successful = 1
GROUP BY
Orders.OrderID,
Orders.[Timestamp],
PaymentProcessorTransactions.[Timestamp]
UNION
SELECT DISTINCT
Orders.OrderID,
Orders.[Timestamp] AS [OrderTimestamp],
PayPalTransactions.[Timestamp] AS [RefundTimestamp],
'PayPal' AS PaymentType,
SUM(PayPalTransactions.Amount) AS [RefundTotal],
(SUM(PayPalTransactions.Amount) - (SUM(PayPalTransactions.Amount) / 1.2)) AS [RefundVatAmount] ,
CASE
WHEN Orders.OrderID IN (
SELECT DISTINCT
OrderItems.OrderID
FROM
OrderItems
INNER JOIN Stock
ON Stock.StockID = OrderItems.StockID
INNER JOIN Models
ON Models.ModelID = Stock.ModelID
INNER JOIN Categories
ON Categories.CategoryID = Models.CategoryID
INNER JOIN VATRates
ON VATRates.CategoryID = Categories.CategoryID
AND VATRates.ShippingCountryID = 1903
WHERE
VATRates.Rate <> @defaultUKVATRate
)
THEN 'True'
ELSE 'False'
END AS [HasVATFreeItems]
FROM
Orders
INNER JOIN
PayPalTransactions
ON PayPalTransactions.OrderID = Orders.OrderID
WHERE
Orders.IsPayPal = 1
AND
Orders.[timestamp] BETWEEN @StartDate AND @EndDate
AND
PayPalTransactions.Method = 5 --rebate
AND
PayPalTransactions.successful = 1
GROUP BY
Orders.OrderID,
Orders.[Timestamp],
PayPalTransactions.[Timestamp]