Hello. I'm wondering if someone can help me with this custom SQL query. My aim is to filter orders from 2018.08.01 to this day
The problem is that with this sql script I get orders from date 2017 to this day
SQL SCRIPT:
SET DATEFORMAT YMD;
DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME
SET @FromDate = '2018.08.01'
SET @ToDate = GETDATE()
SELECT t.CategoryName, CONVERT(VARCHAR(10), o.dReceievedDate, 102) AS 'Order Date', o.nOrderId
AS 'Order ID', oi.ItemNumber AS 'Item ID', 'SKU' = CASE WHEN pis.pkStockItemID IS NOT
NULL THEN pis.ItemNumber WHEN si.pkStockItemID IS NOT NULL THEN si.ItemNumber
WHEN oi.ChannelSKU IS NOT NULL THEN oi.ChannelSKU ELSE 'SKU Not Supplied' END,
'Product Name' = CASE WHEN pis.pkStockItemID IS NOT NULL THEN pis.ItemTitle WHEN
si.pkStockItemID IS NOT NULL THEN si.ItemTitle WHEN sis.cItemName IS NOT NULL
THEN sis.cItemName ELSE 'Product Name Not Supplied' END, o.fTax as 'fTax', o.Subtotal AS 'Subtotal',
o.PostageCostExTax as 'PostageCostExTax', o.fTotalCharge as 'Total Charge',
o.CountryTaxRate as 'CountryTaxRate', 'Currency' = CASE
o.cCurrency WHEN '' THEN 'GBP' WHEN '$' THEN 'USD' WHEN '' THEN 'EUR' WHEN ''
THEN 'GBP' ELSE o.cCurrency END, oi.fPricePerUnit AS 'Product Price', oi.nQty AS
'Quantity', o.fPostageCost AS 'Carriage Amount', ISNULL(ROUND((oi.fPricePerUnit / 100) *
oi.LineDiscount,2), 0) as 'Discount', ISNULL(b.AccountName, '') AS 'Payment Method',
ps.PostalServiceName AS 'Shipping Method', 'Billing Name' =
REPLACE(ISNULL(oia.BillingName,o.cFullName),'"',''), 'Billing Company Name' =
REPLACE(ISNULL(oia.BillingCompany,o.Company),'"',''), 'Billing Address 1' =
REPLACE(ISNULL(oia.BillingAddress1, o.Address1),'"',''), 'Billing Address 2' =
REPLACE(ISNULL(oia.BillingAddress2, o.Address2),'"',''), 'Billing Address 3' =
REPLACE(ISNULL(oia.BillingTown, o.Town),'"',''), 'Billing Address 4' =
REPLACE(ISNULL(oia.BillingRegion, o.Region),'"',''), 'Billing Address 5' =
REPLACE(ISNULL(oia.BillingPostcode, o.cPostCode),'"',''), 'Billing Country' =
ISNULL(oia.BillingCountryName, lc.cCountry), 'Billing Country Code' = CASE
lc.cCountryCode WHEN 'UNK' THEN 'GB' ELSE
isnull(billcountry.cCountryCode,lc.cCountryCode) END, o.cEmailAddress AS 'Customer
Email', o.BuyerPhoneNumber AS 'Customer Telephone', o.cFullName AS 'Shipping Name',
REPLACE(o.Company,'"','') AS 'Shipping Company Name', REPLACE(o.Address1,'"','') AS
'Shipping Address 1', REPLACE(o.Address2,'"','') AS 'Shipping Address 2',
REPLACE(o.Town,'"','') AS 'Shipping Address 3', REPLACE(o.Region,'"','') AS 'Shipping
Address 4', REPLACE(o.cPostCode,'"','') AS 'Shipping Address 5', lc.cCountry AS 'Shipping
Country', lc.cCountryCode AS 'Shipping Country Code', 'Order Status' = CASE o.nStatus
WHEN '1' THEN 'Complete' END, o.Source + ' ' + o.SubSource AS 'Marketplace'
FROM
[Order] o
INNER JOIN OrderItem oi on oi.fkOrderID = o.pkOrderID
INNER JOIN StockItems sis on sis.pkStockID = oi.fkStockID LEFT OUTER JOIN StockItem si on sis.fkStockControlStockItemId =
si.pkStockItemID LEFT OUTER JOIN StockItem pis on pis.pkStockItemID =
oi.fkStockItemId_processed INNER JOIN PostalServices ps on o.fkPostalServiceId =
ps.pkPostalServiceId INNER JOIN ListCountries lc on o.fkCountryId = lc.pkCountryId LEFT OUTER JOIN ProductCategories t on pis.CategoryId = t.CategoryId
left outer join Order_AdditionalInfo oia on oia.fkOrderId = o.pkOrderID LEFT OUTER JOIN
Accounting_Bank b on b.pkBankId = o.fkBankId left outer join
(SELECT cCountry,cCountryCode=MAX(cCountryCode) FROM ListCountries WHERE
bLogicalDelete=0 GROUP BY cCountry) as billcountry on billcountry.cCountry =
oia.BillingCountryName WHERE (oi.fkCompositeParentRowId = '00000000-0000-0000-0000-000000000000' or
oi.fkCompositeParentRowId is null)and o.nStatus = 1 and (o.dReceievedDate BETWEEN
@FromDate AND @ToDate) AND o.dProcessedOn IS NOT NULL AND
o.bProcessed = 1 AND t.CategoryName = 'CCTV Equipment' OR t.CategoryName = 'CCTV Equipment LT' OR t.CategoryName = 'CCTV Camera Accessories' OR t.CategoryName = 'CCTV Cameras' OR t.CategoryName = 'CCTV Dahua' OR t.CategoryName = 'CCTV Lenses' OR t.CategoryName = 'CCTV Recorders' OR t.CategoryName = 'CCTV Surveillance Equipment'
ORDER BY
t.CategoryName
