I am working on SQL Server 2014. I have a query which returns a list of Job orders that will consume Components (with a required quantity ReQ) and at a specific date (Req Dt). I join this list to a stock table that provides for each component, the current available stock and the total of already reserved quantity. The job order required quantity is not yet reserved/confirmed, thus not taken into account in the stock table. The list is ordered by component number and date of need.

For each line consuming the same component, I want to add a colunm that calculates the projected Net Stock (NSt, remaining component quantity) and thus identify future missing components (when the projected Net Stock is 0 or below).

Looping into the list and for each component, the calculation rules would be :

1. For the first line (first component occurence): Net Stock = (Available stock - Reserved quantity - required quantity) from the current line.
2. For the second line (second component occurence): Net Stock = Net Stock from line 1 - required quantity of line 2
3. For the last line: Net Stock N = Net Stock N-1 - required quantity line N

If I use Excel it would look like : enter image description here

Many thanks in advance for help on this topic.

Thanks for your interest Marc and Larnu.

I have already created the SQL code that fetches the primary data; I need to modify the query in order to add 2 columns:

• Calculation of projected Net stock (NST).
• Attribute yes/no for low remaining stock (LSt) but this one I can handle.

The table structure is mainly based on the following columns:

• Job_No=Job order that consumes the needed quantity (ReQ)
• Cmp=Component code
• Req Dt=Need Date
• ReQ=Qty needed (quantity of component needed)
• AvSt=Available Stock from stock table
• ResQ=Total reserved Qty from stock table
• NSt=Net Stock (projected remaining stock)
• LSt="If Net Stock >=0 then No, If Net Stock <0 then Yes".

Available stock and Total reserved Qty are the current values from the stock table at the moment the query is run.

For each component, the first occurence provides the initial Net Stock which is then used to calculate the Net Stock of the next occurence (line). When a new component is found, the same calculation process is restarted.

The difficult points for me are :

• How to use the calculated Net Stock value from the previous line into the current line.
• How to restart the calculation process each time a new component is found into the list.

Here are a set of data with expected results for NSt and LSt:

--My initial SQL:

    SELECT --A.[SCHN - Schedule number] 'Schedule'
    A.[MFNO - Manufacturing order number] 'Job_No'
    ,A.[MTNO - Component number] 'Comp'
    ,A.[RDAT - Reservation date] 'Req dt'
    ,A.[REQT - Reserved quantity] 'ReQ'
    ,C.[STQT - On-hand balance approved] 'AvSt' -- from stock table
    ,C.[REQT - Reserved quantity] 'ResQ'          -- from stock table
      
 /** This is the first projected Net Stock for the first component occurence
       but how to calculate the next occurence line with the previous line result? **/
      ,C.[STQT - On-hand balance approved]-C.[REQT - Reserved quantity]-A.[REQT - Reserved quantity] as 'NSt'

      ,case when (C.[STQT - On-hand balance approved]-C.[REQT - Reserved quantity]-A.[REQT - Reserved quantity])<=0 then 'Yes'
      else 'No' end as 'LSt'
      /* (C.[STQT - On-hand balance approved]-C.[REQT - Reserved quantity]-A.[REQT - Reserved quantity])
        will be replaced by the new projected Net Stock when available*/
     
      ,A.[RGDT - Entry date]
    FROM [JOB ORDERS] A    
    left join [WAREHOUSE STOCK] C
    On A.[MTNO - Component number]=C.[ITNO - Item number] and A.[WHLO - Warehouse]=C.[WHLO - Warehouse]

    where A.[WMST - Material status]<='44' 
            and A.[SCHN - Schedule number]<>0
                and A.[RGDT - Entry date]>20210101
                    and A.[REQT - Reserved quantity]>0
    order by A.[MTNO - Component number] ,A.[RDAT - Reservation date]

CodePudding user response：

You can use SUM as a window function.

• Note the use of ROWS UNBOUNDED PRECEDING, this is necessary if the ORDER BY is non-deterministic, and is in any case more efficient
• Use meaningful table aliases, A B C doesn't really help comprehension
• Good whitespace formatting is essential
• Quote column names with [] not ''
• I strongly recommend you name your columns something shorter that does not require quoting in the first place
• Date constants should be in quotes

SELECT
   --jo.[SCHN - Schedule number] [Schedule]
     jo.[MFNO - Manufacturing order number] [Job_No]
    ,jo.[MTNO - Component number] [Comp]
    ,jo.[RDAT - Reservation date] [Req dt]
    ,jo.[REQT - Reserved quantity] [ReQ]
    ,ws.[STQT - On-hand balance approved] [AvSt] -- from stock table
    ,ws.[REQT - Reserved quantity] [ResQ]          -- from stock table
      
    ,SUM(ws.[STQT - On-hand balance approved] - ws.[REQT - Reserved quantity] - jo.[REQT - Reserved quantity])
     OVER (PARTITION BY jo.[MTNO - Component number], jo.[WHLO - Warehouse]
           ORDER BY jo.[RDAT - Reservation date] ROWS UNBOUNDED PRECEDING
     ) as [NSt]

    ,case when (ws.[STQT - On-hand balance approved] - ws.[REQT - Reserved quantity] - jo.[REQT - Reserved quantity]) <= 0
         then 'Yes'
         else 'No' end as [LSt]

    ,jo.[RGDT - Entry date]

FROM [JOB ORDERS] jo
left join [WAREHOUSE STOCK] ws
    ON jo.[MTNO - Component number] = ws.[ITNO - Item number]
   and jo.[WHLO - Warehouse]  =ws.[WHLO - Warehouse]

where jo.[WMST - Material status] <= '44' 
  and jo.[SCHN - Schedule number] <> 0
  and jo.[RGDT - Entry date] > '20210101'
  and jo.[REQT - Reserved quantity] > 0
order by
  jo.[MTNO - Component number],
  jo.[RDAT - Reservation date];