_PBI_Invoices

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;