API tools faq
paste
levi_davis

_PBI_Invoices

levi_davis
Aug 12th, 2024
823
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 1.67 KB | None | 0 0
raw download clone embed print report diff
  1. SELECT TOP(100)PERCENT
  2.     i.InvoiceDate,
  3.     CONCAT(FORMAT(i.InvoiceDate, 'yyyy'), '-', FORMAT(DATEPART(WEEK, i.InvoiceDate),'00')) AS "Week",
  4.     FORMAT(i.InvoiceDate, 'yyyy-MM') AS "Month",
  5.     CONCAT(FORMAT(i.InvoiceDate, 'yyyy'), '-Q', DATEPART(QUARTER, i.InvoiceDate)) AS "Quarter",
  6.     FORMAT(i.InvoiceDate, 'yyyy') AS "Year",
  7.     dept.Department AS 'Business Division',
  8.     i.BranchID,
  9.     i.SalespersonID,
  10.     i.GUIDInvoice,
  11.     i.InvoiceNumber,
  12.     i.TYPE,
  13.     i.CustomerID,
  14.     pc.Territory,
  15.     ISNULL(ISNULL(pc.Country,i.BillToCountry),'') AS 'CustomerCountry',
  16.     i.SubTotal,
  17.     i.InvoiceDiscountPct,
  18.     i.DiscountAmount,
  19.     i.SalesTax,
  20.     i.TotalAmount,
  21.     (i.TotalAmount - i.SalesTax) AS 'AmountNoTax',
  22.     id.GUIDProduct,
  23.     TRIM(p."_Brand") AS 'Brand',
  24.     id.ProductID,
  25.     p."_PriceListed",
  26.     id.Price,
  27.     id.QtyInvoiced,
  28.     id.Amount,
  29.     id.LineDiscountPct,
  30.     id.InvoiceDiscountAmount,
  31.     (id.Amount  - id.InvoiceDiscountAmount) AS 'LineAmount'
  32. FROM
  33.     "ACCTivate$Haverford".dbo.Invoice i
  34. INNER JOIN
  35.     "ACCTivate$Haverford".dbo.InvoiceDetail id
  36.     ON i.GUIDInvoice = id.GUIDInvoice
  37. LEFT JOIN
  38.     "ACCTivate$Haverford".dbo.Product AS p
  39.     ON id.GUIDProduct = p.GUIDProduct
  40. LEFT JOIN
  41.     "ACCTivate$Haverford".dbo.[_Channel_Partner_Countries] AS pc
  42.     ON i.CustomerID = pc.CustomerID
  43. LEFT JOIN
  44.     "ACCTivate$Haverford".dbo._Department_BranchID_Mapping AS dept
  45.     ON i.BranchID = dept.BranchID
  46. WHERE
  47.     i.InvoiceDate >= {ts '2017-04-01 00:00:00.000'}
  48.     AND i.TYPE <> 'R'
  49.     AND id.LineCancelled = 0
  50.     AND i.OrderDate IS NOT NULL
  51. ORDER BY
  52.     i.InvoiceDate,
  53.     i.InvoiceNumber,
  54.     TRIM(p."_Brand"),
  55.     p.ProductID;
Advertisement
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!