Skip to Content
0

Stock Query & Reports

Apr 13, 2017 at 09:08 AM

95

avatar image

Hi Gurus,

I am needing some help with two reports, please take a look at the requirements below and let me know if you can assist?

1. A report for average monthly sales as well as displaying 3, 6 or 12 months (not a rolling report). It needs to contain:

Group Name, Item number, Item description, Average sales, Highest unit sales qty & value within monthly, 3,6,12 months sales, Item cost and Total Cost (Average sales * item cost)

2. Is it possible to use the average sales to generate an additional report such as one containing:
Min stockholding (Average *factor 1.5)
Max stockholding holding (Min stock holding * factor 3)
Model stockholding (Min + Max / 2)
Total value of model stock (Model stock * item cost)
Total value of max stock (Max Stock * Item cost)

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Johan Hakkesteegt Apr 13, 2017 at 10:58 AM
0

Hi Bruce,

Your question nr 1, leaves some questions, and question nr 2 I will leave to you (give a man a fish, etc.), so here is my interpretation. You may adapt it to your needs. Feel free to play around with it (B1 will not blow up), and come back with any questions that might bring up:

SELECT g.ItmsGrpNam
      ,i.ItemCode
      ,i.ItemName

      /* 3 months */
      ,ISNULL((select AVG(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -3, getdate()) and getdate()), 0) AS [Average Sales 3 months]
      ,ISNULL((select MAX(Quantity) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -3, getdate()) and getdate()), 0) AS [Highest unit sales qty 3 months]
      ,ISNULL((select MAX(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -3, getdate()) and getdate()), 0) AS [Highest unit sales value 3 months]
      ,ISNULL((select SUM(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -3, getdate()) and getdate()), 0) AS [Total Sales 3 months]
      ,ISNULL((select AVG(GrossBuyPr) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -3, getdate()) and getdate()), 0) AS [Average Cost Price 3 months]
      ,ISNULL((select SUM(GrossBuyPr * Quantity) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -3, getdate()) and getdate()), 0) AS [Total Cost 3 months]
      /* 6 months */
      ,ISNULL((select AVG(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -6, getdate()) and getdate()), 0) AS [Average Sales 6 months]
      ,ISNULL((select MAX(Quantity) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -6, getdate()) and getdate()), 0) AS [Highest unit sales qty 6 months]
      ,ISNULL((select MAX(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -6, getdate()) and getdate()), 0) AS [Highest unit sales value 6 months]
      ,ISNULL((select SUM(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -6, getdate()) and getdate()), 0) AS [Total Sales 6 months]
      ,ISNULL((select AVG(GrossBuyPr) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -6, getdate()) and getdate()), 0) AS [Average Cost Price 6 months]
      ,ISNULL((select SUM(GrossBuyPr * Quantity) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -6, getdate()) and getdate()), 0) AS [Total Cost 6 months]
      /* 12 months */
      ,ISNULL((select AVG(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -12, getdate()) and getdate()), 0) AS [Average Sales 12 months]
      ,ISNULL((select MAX(Quantity) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -12, getdate()) and getdate()), 0) AS [Highest unit sales qty 12 months]
      ,ISNULL((select MAX(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -12, getdate()) and getdate()), 0) AS [Highest unit sales value 12 months]
      ,ISNULL((select SUM(LineTotal) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -12, getdate()) and getdate()), 0) AS [Total Sales 12 months]
      ,ISNULL((select AVG(GrossBuyPr) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -12, getdate()) and getdate()), 0) AS [Average Cost Price 12 months]
      ,ISNULL((select SUM(GrossBuyPr * Quantity) 
               from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
               where INV1.ItemCode = i.ItemCode
                 and OINV.DocDate between dateadd(MM, -12, getdate()) and getdate()), 0) AS [Total Cost 12 months]
FROM OITM i
     INNER JOIN OITB g ON i.ItmsGrpCod = g.ItmsGrpCod
WHERE ISNULL((select SUM(LineTotal) 
              from INV1 inner join OINV on INV1.DocEntry = OINV.DocEntry
              where INV1.ItemCode = i.ItemCode
                and OINV.DocDate between dateadd(MM, -12, getdate()) and getdate()), 0) > 0
ORDER BY g.ItmsGrpNam
    ,i.ItemName
    ,i.ItemCode

Regards,

Johan

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Wow, Johan!

Many thanks for the quick reply, it works perfectly and was exactly what I was hoping to see.

Thanks for your help again - it's much appreciated.

Have a great day further.

0