Maximo Open Forum

 View Only

Query Using a Dynamic Date Range for Inventory Transactions for Monthly Reporting

  • 1.  Query Using a Dynamic Date Range for Inventory Transactions for Monthly Reporting

    Posted 12 days ago
    Hello Everyone,

    I am trying to improve upon a query we use to show the previous month's transactions for Inventory. The query will pull all of the items that were issued/returned in the previous month as expected, however when you print out the report, it will also show the old transactions that occurred in previous months, for those items. We are trying to narrow it down so that the report only shows the items issued/returned that previous month, along with the corresponding issue/return dates within that month, instead of all of the extra transactions outside of the previous month. Hopefully this makes sense :-)

    Here is the query being used:

    (siteid = 'JAXPORT') and (status = 'ACTIVE') and
    (exists (select 1 from dbo.matusetrans where (datepart(m, transdate) = datepart(m, dateadd(m, -1, getdate())) and
    datepart(yy, transdate) = datepart(yy, dateadd(m, -1, getdate()))) and
    (datepart(m, lastissuedate) = datepart(m, dateadd(m, -1, getdate())) and
    datepart(yy, lastissuedate) = datepart(yy, dateadd(m, -1, getdate()))) and
    (itemnum = inventory.itemnum and storeloc = inventory.location and itemsetid = inventory.itemsetid and siteid=inventory.siteid)))

    Thank you for the assistance!

    Kathy
    #Administration
    #Analytics
    #Inventory
    #Reporting

    ------------------------------
    Kathy Seabrook
    Jacksonville Port Authority
    ------------------------------