SHOW:
|
|
- or go back to the newest paste.
| 1 | - | WITH |
| 1 | + | SELECT TOP(100)PERCENT |
| 2 | - | p AS ( |
| 2 | + | i.InvoiceDate, |
| 3 | - | SELECT |
| 3 | + | CONCAT(FORMAT(i.InvoiceDate, 'yyyy'), '-', FORMAT(DATEPART(WEEK, i.InvoiceDate),'00')) AS "Week", |
| 4 | - | do."Order", |
| 4 | + | FORMAT(i.InvoiceDate, 'yyyy-MM') AS "Month", |
| 5 | - | p."GUIDProduct", |
| 5 | + | CONCAT(FORMAT(i.InvoiceDate, 'yyyy'), '-Q', DATEPART(QUARTER, i.InvoiceDate)) AS "Quarter", |
| 6 | - | TRIM(p."_Brand") AS 'Brand', |
| 6 | + | FORMAT(i.InvoiceDate, 'yyyy') AS "Year", |
| 7 | - | p."ProductID" |
| 7 | + | dept.Department AS 'Business Division', |
| 8 | - | FROM "ACCTivate$Haverford"."dbo"."tbProduct" AS p |
| 8 | + | i.BranchID, |
| 9 | - | LEFT JOIN "ACCTivate$Haverford"."dbo"."_CR_Demand_Order" AS do |
| 9 | + | i.SalespersonID, |
| 10 | - | ON p."ProductID" = do."ProductID" |
| 10 | + | i.GUIDInvoice, |
| 11 | - | WHERE |
| 11 | + | i.InvoiceNumber, |
| 12 | - | ( |
| 12 | + | i.Type, |
| 13 | - | p."_PriceListed" = 1 |
| 13 | + | i.CustomerID, |
| 14 | - | AND p."ProductID" <> 'PT-STUDIOPRO-inactive' |
| 14 | + | pc.Territory, |
| 15 | - | ) |
| 15 | + | ISNULL(ISNULL(pc.Country,i.BillToCountry),'') AS 'CustomerCountry', |
| 16 | - | OR |
| 16 | + | i.SubTotal, |
| 17 | - | ( |
| 17 | + | i.InvoiceDiscountPct, |
| 18 | - | p."ProductID" = do."ProductID" |
| 18 | + | i.DiscountAmount, |
| 19 | - | ) |
| 19 | + | i.SalesTax, |
| 20 | - | |
| 20 | + | i.TotalAmount, |
| 21 | - | ), |
| 21 | + | (i.TotalAmount - i.SalesTax) AS 'AmountNoTax', |
| 22 | - | pabm AS ( |
| 22 | + | id.GUIDProduct, |
| 23 | - | SELECT |
| 23 | + | TRIM(p."_Brand") AS 'Brand', |
| 24 | - | CASE |
| 24 | + | id.ProductID, |
| 25 | - | WHEN pabm."GUIDProduct" = '2F2A5D97-CBB0-4F56-A36C-4A7F47E82B1D' |
| 25 | + | p."_PriceListed", |
| 26 | - | THEN '5D1AF4A4-8B49-4948-A188-E35D7D0D1B9E' |
| 26 | + | id.Price, |
| 27 | - | ELSE pabm."GUIDProduct" |
| 27 | + | id.QtyInvoiced, |
| 28 | - | END AS "GUIDProduct", |
| 28 | + | id.Amount, |
| 29 | - | pabm."WarehouseID", |
| 29 | + | id.LineDiscountPct, |
| 30 | - | pabm."MonthBegin", |
| 30 | + | id.InvoiceDiscountAmount, |
| 31 | - | pabm."MonthEnd", |
| 31 | + | (id.Amount - id.InvoiceDiscountAmount) AS 'LineAmount' |
| 32 | - | SUM(pabm."DemandQty") AS "DemandQty", |
| 32 | + | FROM |
| 33 | - | SUM(pabm."CancelQty") AS "CancelQty", |
| 33 | + | "ACCTivate$Haverford".dbo.Invoice i |
| 34 | - | SUM(pabm."CancelQty") As "returnQty" |
| 34 | + | INNER JOIN |
| 35 | - | FROM "ACCTivate$Haverford"."dbo"."_HSI_ProductAnalysisByMonth" AS pabm |
| 35 | + | "ACCTivate$Haverford".dbo.InvoiceDetail id |
| 36 | - | WHERE pabm."MonthBegin" BETWEEN {ts '2017-04-01 00:00:00.000'} AND EOMONTH(CURRENT_TIMESTAMP)
|
| 36 | + | ON i.GUIDInvoice = id.GUIDInvoice |
| 37 | - | AND pabm."WarehouseID" IN ('Ret-in', 'B-Stk', 'Main')
|
| 37 | + | LEFT JOIN |
| 38 | - | GROUP BY |
| 38 | + | "ACCTivate$Haverford".dbo.Product AS p |
| 39 | - | CASE |
| 39 | + | ON id.GUIDProduct = p.GUIDProduct |
| 40 | - | WHEN pabm."GUIDProduct" = '2F2A5D97-CBB0-4F56-A36C-4A7F47E82B1D' |
| 40 | + | LEFT JOIN |
| 41 | - | THEN '5D1AF4A4-8B49-4948-A188-E35D7D0D1B9E' |
| 41 | + | "ACCTivate$Haverford".dbo.[_Channel_Partner_Countries] AS pc |
| 42 | - | ELSE pabm."GUIDProduct" |
| 42 | + | ON i.CustomerID = pc.CustomerID |
| 43 | - | END, |
| 43 | + | LEFT JOIN |
| 44 | - | pabm."WarehouseID", |
| 44 | + | "ACCTivate$Haverford".dbo._Department_BranchID_Mapping AS dept |
| 45 | - | pabm."MonthBegin", |
| 45 | + | ON i.BranchID = dept.BranchID |
| 46 | - | pabm."MonthEnd" |
| 46 | + | WHERE |
| 47 | - | ) |
| 47 | + | i.InvoiceDate >= {ts '2017-04-01 00:00:00.000'}
|
| 48 | - | |
| 48 | + | AND i.Type <> 'R' |
| 49 | - | SELECT TOP(100)PERCENT |
| 49 | + | AND id.LineCancelled = 0 |
| 50 | - | p."GUIDProduct", |
| 50 | + | AND i.OrderDate IS NOT NULL |
| 51 | - | p."Brand", |
| 51 | + | |
| 52 | - | p."ProductID", |
| 52 | + | i.InvoiceDate, |
| 53 | - | CASE |
| 53 | + | i.InvoiceNumber, |
| 54 | - | WHEN p."ProductID" LIKE '%-WH%' THEN REPLACE(p."ProductID",'-WH','-xx') |
| 54 | + | TRIM(p."_Brand"), |
| 55 | - | WHEN p."ProductID" LIKE '%-BK%' THEN REPLACE(p."ProductID",'-BK','-xx') |
| 55 | + | p.ProductID; |
