Couldn't post this solution at the time because of a quirk in the forum, had to PM it.

😎

USE tempdb;

GO

/*

Item Location QtyOnHandByLocation SumQtyCommitTotal

Prod1 NJ 10 10

Prod1 NY 10 0

Prod1 FL 0 0

Prod1 PA 0 0

*/

--create the temp table

Create table #SalesLine

(

Novarchar (50) not null

, LocationCodevarchar (50) not null

, QtyCommitint not null

)

create table #ItemLedgerEntry

(

Novarchar (50) not null

, LocationCodevarchar (50) not null

, QtyRemainint not null

)

--insert test data

insert into #SalesLine

(

NO, LocationCode, QtyCommit

)

Values

('0000-6896-31 MISC', 'NJ', '10')

,('0000-6896-31 MISC', 'NY', '0')

insert into #ItemLedgerEntry

(

NO, LocationCode, QtyRemain

)

Values

('0000-6896-31 MISC', 'NJ', '10')

,('0000-6896-31 MISC', 'NY', '10')

,('0000-6896-31 MISC', 'FL', '0')

,('0000-6896-31 MISC', 'PA', '0');

SELECT

IL.No AS Item

,IL.LocationCode AS Location

,IL.QtyRemain AS QtyOnHandByLocation

,ISNULL(SL.QtyCommit,0) AS SumQtyCommitTotal

FROM #ItemLedgerEntry IL

LEFT OUTER JOIN #SalesLine SL

ON IL.LocationCode = SL.LocationCode

AND IL.No = SL.No

DROP TABLE #ItemLedgerEntry;

DROP TABLE #SalesLine;

Results

Item Location QtyOnHandByLocation SumQtyCommitTotal

------------------ --------- ------------------- -----------------

0000-6896-31 MISC NJ 10 10

0000-6896-31 MISC NY 10 0

0000-6896-31 MISC FL 0 0

0000-6896-31 MISC PA 0 0