August 20, 2014 at 3:23 pm
Hello All,
I am trying to tie together tables that show quantities of a product committed to an order and quantities on hand by a location.
My end result should look like the below example.
Item Location QtyOnHandByLocation SumQtyCommitTotal
Prod1 NJ 10 10
Prod1 NY 10 0
Prod1 FL 0 0
Prod1 PA 0 0
So I can see I have 10 items in NJ On Hand and Committed to an order. 10 available in NY but not on an order. Then the other two locations have no quantities.
Below is the CTE but it produces inaccurate results. I've tried running it several different ways by playing with the grouping but have no luck thus far.
--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')
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'
group by ile.No, ile.LocationCode, ile.QtyOnHand
Order by ile.[No]
Please take a look and assist me if possible. I am close to the desired results but can't find a way.
Keith
August 25, 2014 at 12:37 pm
You are joining on "No" between your two CTEs and since all rows have the same No value all items in SumSalesQtyCommit are joined to SumQtyOnHand. add a join condition for location and you will get the desired results.
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]
and sot.LocationCode = ile.LocationCode
where ile.[No] = '0000-6896-31 MISC'
group by ile.No, ile.LocationCode, ile.QtyOnHand
Order by ile.[No]
August 25, 2014 at 2:02 pm
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
August 25, 2014 at 2:22 pm
Thanks Eirikur,
When I run the query in the actual database I get back over 7,000 records for 1 item. I should only get 8 results back for this item. Your query does work well with the test data but not in the actual database.
I'll post back some more values to test with shortly.
August 25, 2014 at 2:36 pm
Robert,
This is very close but when I run it in my production system I only get back 2 records. The results I get back are the results where there is both QtyOnHand and SumQtyCommitTotal. I should see the ItemNo even if there is no QtyOnHand or if there is QtyOnHand and 0 SumQtyCommitTotal.
When I run the Inner Query for the ItemLedgerEntry and specify the item in a where clause I get back 8 records.
When I run the inner query for the SalesLine table I get back 2 records.
Is there a way I can insert a 0 SumQtyCommitTotal record in the SalesLine if a location exists in the ItemLedgerEntry table?
August 25, 2014 at 3:06 pm
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]
August 25, 2014 at 4:56 pm
Hi
I think Eirikur's is right, except the summing CTEs were missed. Try Eirikur's with the CTEs in place.
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];
August 25, 2014 at 6:57 pm
Thanks for the suggestion Mickey but even with the test data I only get two results back.
August 25, 2014 at 7:43 pm
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)
August 25, 2014 at 8:39 pm
OK, Your right I didn't have
left join SumSalesQtyCommit as sot
On sot.[No.] = ile.[Item No.] and sot.[Location Code] = ile.[Location Code]
I had
left join SumSalesQtyCommit as sot
On sot.[No.] = ile.[Item No.]
where sot.[Location Code] = ile.[Location Code]
group by ile.[Item No.], ile.[Location Code], , ile.QtyOnHand, sot.QtyCommit
I think this wraps it up but I'll test more items in the morning.
thanks for being persistent micky!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply