Outer reference to restrict inner group by

Question

I have a series of Receive and Issue transactions. Received inventory can be at different costs per job material item, possibly received in multiple transactions. And there are two possible methods of receipt: Mfg Receipt and Receipt Lines. Where neither exist, the cost defaults to average inventory cost, which is reliably stored in Cost Type. Received cost is not reliably stored in an Issue transaction's Cost Type, so I have to search backward in the receipt transactions. This precedence is handled by COALESCE.

The aim is to return (reverse) issue to inventory at the same cost at which it was received. Subsequent receipts are possible, at a different cost, and returns should be at that cost, and not at costs from receipts prior or subsequent within the same job.

Transaction (prefix imt) joined to Cost Type (prefix int) look like this for Material ID 21:

Each MaterialIssue transactions can pair to each Receipt Lines, so I have to restrict the inner result to a single receive transaction to prevent Cartesian product, without which, I could get multiple results, as in material item 21:

I have tried to accomplish with this CTE, using ROW() ranking, commented with the imaginary desired method, but it's mind-bending and I'm not sure how to go about it:

DECLARE @bShowNonInventory BIT = 1
DECLARE @nCostingMethod INT = 1
DECLARE @vJobID VARCHAR (11) = '23532-02-01'
; WITH
MaterialIssues as (
                    SELECT 
                    MI.imtJobID JobID
                    , MI.imtJobAssemblyID AssemblyID
                    , MI.imtJobMaterialID MaterialID
                    , SUM(
                        (
                        COALESCE(
                            MR.intActualUnitMaterialCost 
                            , RL.intActualUnitMaterialCost 
                            , intUnitMaterialCost
                        ) 

                    ) 
                    * - (intQuantity)
                ) NetCost
                FROM PartTransactions MI -- Material Issue
                INNER JOIN PartTransactionCosts
                    ON imtPartTransactionID = intPartTransactionID


                LEFT JOIN /* get single, most recent received cost */
                    (   SELECT * FROM 
                        (
                            SELECT 
                            imtJobID, imtJobAssemblyID, imtJobMaterialID
                            , imtPartTransactionID
                            , intActualUnitMaterialCost
                            , ROW_Number() 
                               OVER (Partition by imtJobID, imtJobAssemblyID, imtJobMaterialID 
                               Order by imtPartTransactionID DESC) as rn
                               --^ set of results from ranking too broad
                            FROM PartTransactionCosts
                            JOIN PartTransactions
                                on imtPartTransactionID=intPartTransactionID
                            WHERE imtTableName='MfgReceipts'
                            AND intCostType = 4
                            --< need outer reference here somehow: imtPartTransactionID < (earlier) MI.imtPartTransactionID
                        ) as d 
                    WHERE   rn=1
                    
                    ) MR -- Receipt by Mfg Receipt
                    ON  MI.imtJobID=MR.imtJobID 
                    AND MI.imtJobAssemblyID=MR.imtJobAssemblyID
                    AND MI.imtJobMaterialID=MR.imtJobMaterialID

                    AND MR.imtPartTransactionID < MI.imtPartTransactionID --< dubious restriction on inner result

                LEFT JOIN 
                    (   SELECT * FROM 
                        (
                            SELECT imtJobID, imtJobAssemblyID, imtJobMaterialID
                            , intActualUnitMaterialCost, imtPartTransactionID 
                            , ROW_Number() 
                               OVER (Partition by imtJobID, imtJobAssemblyID, imtJobMaterialID 
                               Order by imtPartTransactionID DESC) as rn
                            FROM PartTransactionCosts
                            JOIN PartTransactions
                                on imtPartTransactionID=intPartTransactionID
                            WHERE imtTableName='ReceiptLines'
                            AND intCostType = 4
                            --< need outer reference here somehow: imtPartTransactionID < (earlier) MI.imtPartTransactionID
                        ) as d 
                    WHERE rn=1 
                    ) RL -- Receipt by ReceiptLine
                    ON  MI.imtJobID=RL.imtJobID 
                    AND MI.imtJobAssemblyID=RL.imtJobAssemblyID
                    AND MI.imtJobMaterialID=RL.imtJobMaterialID

                    AND RL.imtPartTransactionID < MI.imtPartTransactionID --< dubious restriction on inner result

                WHERE 
                        imtSource = 3
                    AND imtNonInventoryTransaction <= @bShowNonInventory                       
                    
                    AND intCostType = @nCostingMethod
                            
                GROUP BY MI.imtJobID 
                    , MI.imtJobAssemblyID 
                    , MI.imtJobMaterialID 
)
-- test query:
Select * from 

MaterialIssues
WHERE JobID = @vJobID

Note that this problem is very similar to Restricting inner query with outer query atttribute - but I don't see a requirement to restrict the inner set to one, most recent, but not subsequent result.

In summary, the net result of quantities received and (return) issued for item 21 is zero, but the net cost should also be zero, but is not correct.

Thank you in advance for your suggestions.

EDIT: DDL and sample data:

USE [master]
GO

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'test', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'test_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_log.ldf' , SIZE = 199680KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

USE test 

CREATE TABLE [test].dbo.[PartTransactions](
    [imtPartTransactionID] [int] NOT NULL,
    [imtTableName] [nvarchar](30) NOT NULL,
    [imtTransactionType] [tinyint] NOT NULL,
    [imtSource] [tinyint] NOT NULL,
    [imtNonInventoryTransaction] [bit] NOT NULL,
    [imtJobID] [nvarchar](20) NOT NULL,
    [imtJobAssemblyID] [int] NOT NULL,
    [imtJobMaterialID] [int] NOT NULL,
    [imtPartID] [nvarchar](30) NOT NULL,

) ON [PRIMARY]
GO
CREATE TABLE [test].[dbo].[PartTransactionCosts](
    [intPartTransactionID] [int] NOT NULL,
    [intPartTransactionCostID] [int] NOT NULL,
    [intCostType] [tinyint] NOT NULL,
    [intQuantity] [numeric](15, 5) NOT NULL,
    [intUnitMaterialCost] [numeric](15, 5) NOT NULL,
    [intActualUnitMaterialCost] [numeric](15, 5) NOT NULL,
) ON [PRIMARY]
GO
-- USE [test]
GO
INSERT [dbo].[PartTransactions] ([imtPartTransactionID], [imtTableName], [imtTransactionType], [imtSource], [imtNonInventoryTransaction], [imtJobID], [imtJobAssemblyID], [imtJobMaterialID], [imtPartID]) VALUES (117718, N'MaterialIssueLines', 2, 3, 0, N'23532-02-01', 0, 21, N'26271/2GC')
GO
INSERT [dbo].[PartTransactions] ([imtPartTransactionID], [imtTableName], [imtTransactionType], [imtSource], [imtNonInventoryTransaction], [imtJobID], [imtJobAssemblyID], [imtJobMaterialID], [imtPartID]) VALUES (117719, N'MfgReceipts', 1, 2, 1, N'23532-02-01', 0, 21, N'26271/2GC')
GO
INSERT [dbo].[PartTransactions] ([imtPartTransactionID], [imtTableName], [imtTransactionType], [imtSource], [imtNonInventoryTransaction], [imtJobID], [imtJobAssemblyID], [imtJobMaterialID], [imtPartID]) VALUES (117726, N'MaterialIssueLines', 2, 3, 0, N'23532-02-01', 0, 21, N'26271/2GC')
GO
INSERT [dbo].[PartTransactions] ([imtPartTransactionID], [imtTableName], [imtTransactionType], [imtSource], [imtNonInventoryTransaction], [imtJobID], [imtJobAssemblyID], [imtJobMaterialID], [imtPartID]) VALUES (117725, N'MfgReceipts', 1, 2, 1, N'23532-02-01', 0, 21, N'26271/2GC')
GO
INSERT [dbo].[PartTransactions] ([imtPartTransactionID], [imtTableName], [imtTransactionType], [imtSource], [imtNonInventoryTransaction], [imtJobID], [imtJobAssemblyID], [imtJobMaterialID], [imtPartID]) VALUES (117727, N'MaterialIssueLines', 2, 3, 0, N'23532-02-01', 0, 21, N'26271/2GC')
GO
INSERT [dbo].[PartTransactions] ([imtPartTransactionID], [imtTableName], [imtTransactionType], [imtSource], [imtNonInventoryTransaction], [imtJobID], [imtJobAssemblyID], [imtJobMaterialID], [imtPartID]) VALUES (117513, N'MfgReceipts', 1, 2, 1, N'23532-02-01', 0, 21, N'26271/2GC')
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117718, 1, 1, CAST(555.00000 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117718, 2, 2, CAST(555.00000 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117718, 3, 3, CAST(555.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117718, 4, 4, CAST(555.00000 AS Numeric(15, 5)), CAST(1.24865 AS Numeric(15, 5)), CAST(1.24865 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117719, 1, 1, CAST(555.00000 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117719, 2, 2, CAST(555.00000 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117719, 3, 3, CAST(555.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117719, 4, 4, CAST(555.00000 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117726, 1, 1, CAST(1100.00000 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117726, 2, 2, CAST(1100.00000 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117726, 3, 3, CAST(1100.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117726, 4, 4, CAST(1100.00000 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117725, 1, 1, CAST(555.00000 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117725, 2, 2, CAST(555.00000 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117725, 3, 3, CAST(555.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117725, 4, 4, CAST(555.00000 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117727, 1, 1, CAST(10.00000 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117727, 2, 2, CAST(10.00000 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117727, 3, 3, CAST(10.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117727, 4, 4, CAST(10.00000 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)), CAST(1.16410 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117513, 1, 1, CAST(555.00000 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)), CAST(1.61513 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117513, 2, 2, CAST(555.00000 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)), CAST(1.53503 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117513, 3, 3, CAST(555.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)), CAST(0.00000 AS Numeric(15, 5)))
GO
INSERT [dbo].[PartTransactionCosts] ([intPartTransactionID], [intPartTransactionCostID], [intCostType], [intQuantity], [intUnitMaterialCost], [intActualUnitMaterialCost]) VALUES (117513, 4, 4, CAST(555.00000 AS Numeric(15, 5)), CAST(1.24865 AS Numeric(15, 5)), CAST(1.24865 AS Numeric(15, 5)))
GO

UPDATE: What this does.

First of all, this is from a DB backend for an ERP system called M1. Its table structures are immutable.

Receiving material to inventory or directly to a job is done through "part transactions." A part transaction joins to one of 4 possible part transaction cost types: 1) average cost 2) Last cost (regardless of the job in question) 3) Standard cost 4) Actual cost (as received directly to a job). Which is to be used is fixed by company-wide accounting policy settings.

Average cost is used for transactions that issue to a job from inventory, or return to inventory (unused) from a job. This is calculated in application code (VB) upon each transaction.

Material issues and returns NOT involving inventory are received directly to a job through various kinds of receipts, such as from a Purchase Order or Manufacturing Receipt (tables: ReceiptLines, MfgReceipt) - and a number of other possible methods (e.g. DMR, RMI, Inspection, Scrap, Shipment, Adjustment). These entities do not have the same footprint and are stored in different tables.

Unfortunately, the application code does not reliably store the "received" cost to a job via Receipt (PO) or Manufacturing receipt, so the data in PartTransactionCost (type 4, Actual) cannot be used as intended. Usually it does calculate correctly, but mysteriously sometimes it doesn't. It's a bug.

Therefore the only reliable way to return materials from a job at the same cost at which they were recieved by any kind of receipt, is to get the actual cost from the applicable receipt record. The COALESCE function selects the non-null join, in order of precedence, and where no receipt is found (i.e. all null results), the cost of last resort is the Average cost.

In the sample data given, There was one receipt of 555 quantity of item 21 at a unit cost of 1.25, then all 555 were returned (i.e. unused). This is done through a MaterialIssue transaction (whose sign is positive, though MaterialIssues have a negative Qty), resulting in a Net Quantity 0, Net Cost 0.00.

Then two more receipts were done for a total quantity of 1100, but at a different recieved unit cost than that of the first receipt. Then two more return transactions occured resulting in an net issue quantity again of 0. However, the code I inherited did not result in a net cost of zero, because the simple join produced a cross product of 3 x 3 receive and return transactions, for a spurious result of 9 components in the sum. So the net returned cost was wrong by multiples. This error was not noticed for a long time because that sequence of transactions is rare.

It must be noted that in this example the Last costs happen to be correct, but since they cannot be relied upon, I needed to get the actual received cost from the receipt record (table: MfgReceipts), and not from the receipt transaction. And the same technique is needed for other reciept types, such as in the table ReceiptLines. But only one receipt record can be used. In the solution provided, the last receipt's actual cost is returned only once, and used in the return transaction in lieu of the stored "last cost" - this results in a net issue quantity of zero and a net cost of zero.

Take the time to post a [mre] here. Those images of large datasets, which we have no way of consuming, are going to be an instant "turn off" for many of the users here. Don't post data as images post it as text; preferably as DDL and DML statement. — Thom A, Apr 15 '21 at 14:15
Looks like you need lateral join ([`OUTER APPLY`](https://learn.microsoft.com/ru-ru/sql/t-sql/queries/from-transact-sql?view=sql-server-ver15#using-apply) in T-SQL). But your query is too long to dig into it, figure out is it really what you need and write this as an answer — astentx, Apr 15 '21 at 14:55
@JosephShirk what do you mean here? If you mean the vote, they are anonymous; I didn't say anywhere the vote was mine. — Thom A, Apr 15 '21 at 20:56
Encouraged by your (now) excellent sample data, I took a look but I'm getting an error on the line `AND RL.imtPartTransactionID < MI.imtPartTransactionID --< dubious restriction on inner result`, SSMS can't resolve RL.imtPartTransactionID, I think you just need to added it to your SELECT list a dozen lines up. I don't have an answer, to the underlying problem, but fixing that will help others who happen to look. — Robert Sheahan, Apr 15 '21 at 23:17
@RobertSheahan good catch, thanks. I just tested the fixed code against 'test' DB and it runs now. — Joseph Shirk, Apr 16 '21 at 11:39
@astentx your suggestion is probably the way. I have looked into OUTER APPLY several times before and failed to grok it. I will spend time with this and see where it leads. — Joseph Shirk, Apr 16 '21 at 11:43
@JosephShirk Nothing special, just reference the column of the outer table. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017l&fiddle=5af2ca87ad7a94d1a6c16e8ba8bb1bba) — astentx, Apr 16 '21 at 11:50
Please do not edit solution announcements into the question. Accept (i.e. click the "tick" next to it) one of the existing answer, if there are any. You can also create your own answer, and even accept it, if your solution is not yet covered by an existing answer. — Yunnosch, Apr 16 '21 at 18:01
I'm really struggling to understand exactly what this query is supposed to do, could you take a stab at expanding your definition of the calculation this query is supposed to implement? For example, you mention returning inventory, but what in the structure you've given distinguishes a return from receiving? For TransactionCosts, why are there 4 types and how does that affect us, are we only interested in CostType=4? Could you walk us through a detailed hand calculation of what the query should do for one record? I saw the comment above mine, has this been solved? — Robert Sheahan, Apr 16 '21 at 18:34
@RobertSheahan done for the greater good. Yes, my solution posted works, but I am not allowed to accept my own answer. — Joseph Shirk, Apr 19 '21 at 19:59

Joseph Shirk · Answer 1 · 2021-04-19T20:26:13.507

The essence of the solution:

-- solved by example of answer 1-1) at 
-- https://stackoverflow.com/questions/9275132/real-life-example-when-to-use-outer-cross-apply-in-sql

SELECT *
    
FROM 
    (SELECT 
        MI.imtJobID JobID
        , MI.imtJobAssemblyID AssemblyID
        , MI.imtJobMaterialID MaterialID
        , MI.imtPartTransactionID
        , intQuantity

     FROM PartTransactions MI -- Material Issue
     INNER JOIN PartTransactionCosts
          ON imtPartTransactionID = intPartTransactionID
     WHERE imtSource=3 
     AND intCostType = 4 -- Average cost, last resort
    ) MI
OUTER APPLY
    (SELECT TOP 1
        imtJobID JobID
        , imtJobAssemblyID AssemblyID
        , imtJobMaterialID MaterialID
        , intActualUnitMaterialCost, imtPartTransactionID 
        , imtTableName, intCostType
        
     FROM PartTransactions PTI JOIN PartTransactionCosts PTCI
        on PTI.imtPartTransactionID=PTCI.intPartTransactionID
        
     WHERE imtTableName='MfgReceipts'
     AND intCostType = 4
     AND PTI.imtJobID=MI.JobID AND PTI.imtJobAssemblyID=MI.AssemblyID AND PTI.imtJobMaterialID=MI.MaterialID

     AND PTI.imtPartTransactionID < MI.imtPartTransactionID 
        -- most recent recieved cost prior to this issuance
         
     ORDER BY PTI.imtPartTransactionID DESC
    ) MR

order by MI.MaterialID, MI.imtPartTransactionID

and whose result is more easily visualized thus:

Full implementation looks like this:

; WITH 
 MaterialIssues as (
        SELECT 
        MI.imtJobID JobID
        , MI.imtJobAssemblyID AssemblyID
        , MI.imtJobMaterialID MaterialID
        , 
        /* abstraction for different job types & sources*/
            (
            COALESCE(
                MR.intActualUnitMaterialCost 
                , RL.intActualUnitMaterialCost 
                , intUnitMaterialCost
            ) 
            + intUnitLaborCost 
            + intUnitOverheadCost 
            + intUnitSubcontractCost 
            + intUnitDutyCost 
            + intUnitFreightCost 
            + intUnitMiscCost
        ) UnitCost 
        , intQuantity 
        Quantity
        , imtSource
        , imtJobType
        , imtNonInventoryTransaction
        , imtReceiptID
        , imtTableName
    FROM PartTransactions MI
    INNER JOIN PartTransactionCosts
        ON imtPartTransactionID = intPartTransactionID
    LEFT JOIN Warehouses
        ON imtPartWarehouseLocationID = imwWarehouseID

    OUTER APPLY /* get single, most recent receieved cost */
        (   
            SELECT TOP 1
            imtJobID JobID, imtJobAssemblyID AssemblyID, imtJobMaterialID MaterialID
            , imtPartTransactionID
            , intActualUnitMaterialCost
            
            FROM PartTransactionCosts   PTCI
            JOIN PartTransactions       PTI 
                on imtPartTransactionID=intPartTransactionID
            WHERE imtTableName='MfgReceipts'
            AND intCostType = 4

            AND PTI.imtJobID=MI.imtJobID
            AND PTI.imtJobAssemblyID=MI.imtJobAssemblyID
            AND PTI.imtJobMaterialID=MI.imtJobMaterialID
            
            AND PTI.imtPartTransactionID < MI.imtPartTransactionID
            ORDER BY PTI.imtPartTransactionID DESC
        ) MR

    OUTER APPLY 
        (
            SELECT TOP 1
            imtJobID JobID, imtJobAssemblyID AssemblyID, imtJobMaterialID MaterialID
            , imtPartTransactionID
            , intActualUnitMaterialCost
            
            FROM PartTransactionCosts   PTCI
            JOIN PartTransactions       PTI 
                on imtPartTransactionID=intPartTransactionID
            WHERE imtTableName='ReceiptLines'
            AND intCostType = 4

            AND PTI.imtJobID=MI.imtJobID
            AND PTI.imtJobAssemblyID=MI.imtJobAssemblyID
            AND PTI.imtJobMaterialID=MI.imtJobMaterialID
            
            AND PTI.imtPartTransactionID < MI.imtPartTransactionID 
            ORDER BY PTI.imtPartTransactionID DESC
        ) RL

    WHERE 
            imtTransactionDate < @dEndDate
        AND imtTransactionDate >= @dStartDate
        AND (
            imtNonNettable = 0
            OR (
                imtNonNettable <> 0
                AND ISNULL(imwDoNotIncludeInJobCosts, 0) = 0
                )
            )
        AND intCostType = @nCostingMethod
                /* always 1 */
    
)
, PartTransactionMaterialCosts as (
    SELECT JobID, AssemblyID
        , SUM( 
                UnitCost
                * 
                (
                    (
                        CASE 
                            WHEN imtSource = 3
                            /*MaterialIssueLines*/
                                THEN - 1
                            ELSE 1
                            END
                    ) * Quantity
                )
            ) 
        NetCost /*NOTE: negated conditionally on Source 3*/
    FROM    MaterialIssues
    WHERE
            imtNonInventoryTransaction <= @bShowNonInventory
        AND imtSource   IN(2, 3)
        AND imtJobType  IN(1, 3)
        
        AND imtReceiptID = ''
        AND Upper(imtTableName) NOT IN ('RECEIPTLINES', 'MFGRECEIPTS', 'MFGRECEIPTCOMPONENTS', 'RECEIPTCOMPONENTS')

    GROUP BY JobID, AssemblyID
)
-- test:
Select * from PartTransactionMaterialCosts

Thanx to @astentx for pointing me in the right direction! – Joseph Shirk Apr 19 '21 at 20:00 — Joseph Shirk, Apr 19 '21 at 20:00

Outer reference to restrict inner group by

1 Answers1