That's interesting
From this drop table #salesline;
drop table #ItemLedgerEntry;
--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', '150')
,('0000-6896-31 MISC', 'NY', '62')
insert into #ItemLedgerEntry
(
NO, LocationCode, QtyRemain
)
Values
('0000-6896-31 MISC', 'NJ', '150')
,('0000-6896-31 MISC', 'NY', '966')
,('0000-6896-31 MISC', 'FL', '240')
,('0000-6896-31 MISC', 'ND', '8')
,('0000-6896-31 MISC', 'OD', '143')
,('0000-6896-31 MISC', 'LA', '0')
,('0000-6896-31 MISC', 'TRANSFER', '0')
,('0000-6896-31 MISC', 'DROP', '0');
WITH SumSalesQtyCommit AS
(
SELECT [No]
, [LocationCode]
, Sum([QtyCommit]) AS SumQtyCommitTotal
FROM #SalesLine
GROUP BY [No], [LocationCode]
),
SumQtyOnHand AS
(
SELECT [No]
, [LocationCode]
, SUM([QtyRemain]) AS QtyOnHand
FROM #ItemLedgerEntry
GROUP BY [No], [LocationCode]
)
SELECT ile.[No] AS ItemNo
, ile.[LocationCode] AS LocationCode
, ile.QtyOnHand AS QtyOnHandByLocation
, isnull(sot.SumQtyCommitTotal, 0) AS SumQtyCommitTotal
FROM SumQtyOnHand AS ile
LEFT JOIN SumSalesQtyCommit AS sot
ON sot.[No] = ile.[No] AND sot.LocationCode = ile.LocationCode
WHERE ile.[No] = '0000-6896-31 MISC'
ORDER BY ile.[No];
I get
ItemNo LocationCode QtyOnHandByLocation SumQtyCommitTotal
-------------------------------------------------- -------------------------------------------------- ------------------- -----------------
0000-6896-31 MISC DROP 0 0
0000-6896-31 MISC FL 240 0
0000-6896-31 MISC LA 0 0
0000-6896-31 MISC ND 8 0
0000-6896-31 MISC NJ 150 150
0000-6896-31 MISC NY 966 62
0000-6896-31 MISC OD 143 0
0000-6896-31 MISC TRANSFER 0 0
(8 row(s) affected)