Ok,
Here is some more sample data.
If you run the Inner query for #ItemLedgerEntry you will see 8 results appear.
For the Locations that have no records for SumQtyCommitTotal in the #SalesLine table a 0 should appear.
The CTE should then return 8 records even if no records are in the #SaleLine table.
Please let me know if any more information is needed. Everyone's help has been very appreciated. Especially Greg Snidow for his PM and Eirikur.
use tempdb
go
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
, sum(isnull(sot.SumQtyCommitTotal, 0)) as SumQtyCommitTotal
From SumQtyOnHand as ile
left join SumSalesQtyCommit as sot
On sot.[No] = ile.[No]
where ile.[No] = '0000-6896-31 MISC' and ile.locationcode = sot.locationcode
group by ile.No, ile.LocationCode, ile.QtyOnHand
Order by ile.[No]