Historical IV Trial Balance - Per Period

Several Customers are encountered with the inquiry of having a detailed report for “Historical” Inventory Balances per Item per Period as shown below;

>> For running total , create the view below and proceed with the next step on the
HITB Per Period (Cumulative) –  Inventory Balance on a Prior Date

The script below retrieves the data from Historical Inventory Trial Balance Tables (SEE30303), to provide both quantity and cost balances per item.

---Tables Included:

• SY40100| Period Setup
• SEE30303|Historical Inventory Trial Balance

CREATE VIEW V_HITB_Per_Period
AS
   SELECT  Z.Year AS Trx_Year,
           Z.Period,
           Z.Item,
           Z.[Quantity] AS QuantityBalance,
           Z.[Cost] AS CostBalance
           FROM    ( SELECT X.[Year],
                            X.[Period],
                            X.[Date],
                            X.[Item],
                            X.[Quantity],
                            X.[Cost]
                            FROM  
( SELECT E.[ITEMNMBR] AS Item,
         E.[Year1] AS Year,
         E.[PeriodID] AS Period ,
         ISNULL(F.[Quantity], 0) AS Quantity,
         ISNULL(F.[Cost], 0) AS Cost ,
         ( E.[Year1] * 365 ) + ( E.[PeriodID] * 30 ) 
         AS Date FROM 
         (SELECT  Z.[ITEMNMBR] ,
                  D.[Year1],
                  D.[PeriodID]
          FROM     
          ( SELECT 
          DISTINCT
          Year1 ,
          PeriodID ,
          DATEADD(mm, 1,
          CONVERT(DATETIME, '01/'
          + CAST(PeriodID AS VARCHAR(2))
          + '/'
          + CAST(YEar1 AS VARCHAR(4)), 103)) AS Date
          FROM  SY40100 AS X
          WHERE PeriodID <> 0
          ) AS D
       INNER JOIN (SELECT
                   Y.[ITEMNMBR],
                   MIN(Y.[DOCDATE]) AS Date
                   FROM
                   [SEE30303] AS Y
                   GROUP BY Y.[ITEMNMBR]
                   ) AS Z ON D.Date > Z.Date
                   ) AS E
                   LEFT OUTER JOIN
                   (
                   SELECT 
                   YEAR([DOCDATE]) AS Year ,
                   MONTH([DOCDATE]) AS Period ,
                   [ITEMNMBR] AS Item,
                   SUM([TRXQtyInBase]) AS Quantity,
                   SUM([EXTDCost]) AS Cost
                   FROM    SEE30303
                   GROUP BY YEAR([DOCDATE]),
                   MONTH([DOCDATE]),
                   [ITEMNMBR]
                   ) AS F ON E.ITEMNMBR = F.Item
                   AND E.PeriodID = F.Period
                   AND E.Year1 = F.Year
                   ) AS X
                   GROUP BY  X.[Year],
                             X.[Period],
                             X.[Date],
                             X.[Item],
                             X.[Quantity],
                             X.[Cost]
                   ) AS Z

Helping Note !
The script could be further enhanced in order to include further "drill down" details such as Inventory Account, Location and other inventorial parameters.

Best Regards,
Mahmoud M. AlSaadi