web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics RMS (Archived) / Changing SQL to report...
Microsoft Dynamics RMS (Archived)

Changing SQL to report on all items

(0) ShareShare
ReportReport
Posted on by Greg Shepherd 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

I have the same question (0)
  • Ryan Sakry Profile Picture
    Ryan Sakry 3,425 on at

    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.

  • Ron Rahhal Profile Picture
    Ron Rahhal 545 on at

    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

  • Gerald Rothaus Profile Picture
    Gerald Rothaus 2,934 on at

    Why are you querying the transaction tables at all?

    -Jerry

    JR Data Inc

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Congratulations to the February Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics RMS (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans