|
PreQuery1 = "IF EXISTS (SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'View_Item_Sales_History') DROP
VIEW View_Item_Sales_History"
PreQuery2 =
<BEGIN>
CREATE View
View_Item_Sales_History AS
SELECT Item.ID AS ItemID, SUM(TransactionEntry.Quantity) AS YTD_QTY,
SUM(TransactionEntry.Price *
TransactionEntry.Quantity) AS YTD_Sales,
SUM(TransactionEntry.Cost *
TransactionEntry.Quantity) AS YTD_Cost,
(SELECT SUM(TransactionEntry.Quantity)
FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE())
AS VARCHAR) AND GETDATE()) AND
A.ID=Item.ID) AS MTD_QTY,
(SELECT SUM(TransactionEntry.Price *
TransactionEntry.Quantity) FROM TransactionEntry
INNER JOIN [Transaction]
WITH(NOLOCK) ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber LEFT
JOIN Item A WITH(NOLOCK) ON TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE())
AS VARCHAR) AND GETDATE()) AND
A.ID=Item.ID) AS MTD_Sales,
(SELECT SUM(TransactionEntry.Cost *
TransactionEntry.Quantity) FROM TransactionEntry
INNER JOIN [Transaction]
WITH(NOLOCK) ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber LEFT
JOIN Item A WITH(NOLOCK) ON TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
CAST(MONTH(GETDATE()) AS VARCHAR) + '/1/' + CAST(YEAR(GETDATE())
AS VARCHAR) AND GETDATE()) AND
A.ID=Item.ID) AS MTD_Cost,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-1,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,0,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS M1_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-2,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,-1,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS M2_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-3,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,-2,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS M3_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-4,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,-3,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS M4_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-5,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,-4,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS M5_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-6,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,-5,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS M6_QTY,
(SELECT SUM(TransactionEntry.Price *
TransactionEntry.Quantity) FROM TransactionEntry
INNER JOIN [Transaction]
WITH(NOLOCK) ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber LEFT
JOIN Item A WITH(NOLOCK) ON TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-1,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,0,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS LM_Sales,
(SELECT SUM(TransactionEntry.Cost *
TransactionEntry.Quantity) FROM TransactionEntry
INNER JOIN [Transaction]
WITH(NOLOCK) ON TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber LEFT
JOIN Item A WITH(NOLOCK) ON TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(MONTH,-1,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(MONTH,0,CAST(MONTH(GETDATE()) as VARCHAR) + '/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR)))
AND A.ID=Item.ID) AS LM_Cost,
(SELECT
SUM(PurchaseOrderEntry.QuantityOrdered -
PurchaseOrderEntry.QuantityReceivedToDate)
FROM PurchaseOrderEntry WHERE
PurchaseOrderEntry.ItemID = Item.ID) AS OnOrder,
(SELECT SUM(TransactionEntry.Quantity)
FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(YEAR,-1,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(YEAR,0,'1/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID = Item.ID) AS LY_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(YEAR,-2,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(YEAR,-1,'1/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID = Item.ID) AS LY2_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(YEAR,-3,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(YEAR,-2,'1/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID = Item.ID) AS LY3_QTY,
(SELECT
SUM(TransactionEntry.Quantity) FROM TransactionEntry INNER JOIN [Transaction]
WITH(NOLOCK) ON
TransactionEntry.TransactionNumber = [Transaction].TransactionNumber
LEFT JOIN Item A WITH(NOLOCK) ON
TransactionEntry.ItemID = A.ID
WHERE ([Transaction].Time BETWEEN
DATEADD(YEAR,-4,'1/1/' + CAST(YEAR(GETDATE()) AS VARCHAR))
AND DATEADD(YEAR,-3,'1/1/' +
CAST(YEAR(GETDATE()) AS VARCHAR))) AND A.ID = Item.ID) AS LY4_QTY
FROM
TransactionEntry INNER JOIN [Transaction] WITH(NOLOCK) ON
TransactionEntry.TransactionNumber =
[Transaction].TransactionNumber LEFT
JOIN Item WITH(NOLOCK) ON TransactionEntry.ItemID = Item.ID
LEFT JOIN Category ON
Item.CategoryID=Category.ID
WHERE [Transaction].Time > '1/1/'
+ CAST(YEAR(GETDATE()) AS VARCHAR)
Group BY Item.ID
<END>
TablesQueried =
"FROM View_Item_Sales_History LEFT JOIN Item WITH(NOLOCK) ON Item.ID =
View_Item_Sales_History .ItemID LEFT JOIN Supplier WITH(NOLOCK) ON
Item.SupplierID = Supplier.ID LEFT JOIN Department WITH(NOLOCK) ON
Item.DepartmentID = Department.ID LEFT JOIN Category WITH(NOLOCK) ON
Item.CategoryID = Category.ID "
SelCriteria =
""
GroupBy =
""
SortOrder =
"Item.ItemLookupCode"
End ReportSummary
|