SELECT TOP(100)PERCENT
    i.InvoiceDate,
	CONCAT(FORMAT(i.InvoiceDate, 'yyyy'), '-', FORMAT(DATEPART(WEEK, i.InvoiceDate),'00')) AS "Week",
	FORMAT(i.InvoiceDate, 'yyyy-MM') AS "Month",
	CONCAT(FORMAT(i.InvoiceDate, 'yyyy'), '-Q', DATEPART(QUARTER, i.InvoiceDate)) AS "Quarter",
	FORMAT(i.InvoiceDate, 'yyyy') AS "Year",
    dept.Department AS 'Business Division',
    i.BranchID,
	i.SalespersonID,
    i.GUIDInvoice, 
    i.InvoiceNumber, 
    i.Type,
    i.CustomerID,
	pc.Territory,
    ISNULL(ISNULL(pc.Country,i.BillToCountry),'') AS 'CustomerCountry',
    i.SubTotal, 
    i.InvoiceDiscountPct,
    i.DiscountAmount,
    i.SalesTax, 
    i.TotalAmount,
    (i.TotalAmount - i.SalesTax) AS 'AmountNoTax',
    id.GUIDProduct,
    TRIM(p."_Brand") AS 'Brand', 
    id.ProductID,
    p."_PriceListed",
    id.Price, 
    id.QtyInvoiced,
    id.Amount,
    id.LineDiscountPct,
    id.InvoiceDiscountAmount,
    (id.Amount  - id.InvoiceDiscountAmount) AS 'LineAmount'
FROM 
    "ACCTivate$Haverford".dbo.Invoice i
INNER JOIN
    "ACCTivate$Haverford".dbo.InvoiceDetail id
    ON i.GUIDInvoice = id.GUIDInvoice
LEFT JOIN
    "ACCTivate$Haverford".dbo.Product AS p
    ON id.GUIDProduct = p.GUIDProduct
LEFT JOIN
    "ACCTivate$Haverford".dbo.[_Channel_Partner_Countries] AS pc
    ON i.CustomerID = pc.CustomerID
LEFT JOIN
    "ACCTivate$Haverford".dbo._Department_BranchID_Mapping AS dept
    ON i.BranchID = dept.BranchID
WHERE 
    i.InvoiceDate >= {ts '2017-04-01 00:00:00.000'}
    AND i.Type <> 'R' 
    AND id.LineCancelled = 0
	AND i.OrderDate IS NOT NULL
ORDER BY 
    i.InvoiceDate,
    i.InvoiceNumber,
    TRIM(p."_Brand"), 
    p.ProductID;