You’re offline. This is a read only version of the page.
45
Hello,
I am trying to change the SQL below to report on all items, not just items with sales (i.e. items with at least one transaction entry). The sql as it stands below returns MTD and YTD information. I have added LYTD, and LYTD-1, etc., to retrieve data for 2009, 2008, etc. It looks like it is reporting on only items that have at least one transaction entry in the current year, so that I do not see items that have not sold this year, but might have sold in previous years. I would like to change the sql to report on all items. Does any one know how to do this? Thanks! Greg
|
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
|
*This post is locked for comments
Why are you querying the transaction tables at all?
-Jerry
JR Data Inc
I wrote a report that is pretty close to what you want. You can contact me at ron AT bestPOSsales DOT com and I'll be happy to help you.
Ron Rahhal
Best POS Sales
The problem starts with your joins. If you want to report on all items, your primary table must be Items. If you start from TransactionEntry, you will only see at most, only what is in this table.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (