SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using a CTE to aggregate seperate tables


Using a CTE to aggregate seperate tables

Author
Message
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 479
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
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyCommit int not null
)

create table #ItemLedgerEntry
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyRemain int 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
Robert klimes
Robert klimes
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2679 Visits: 3422
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]



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Eirikur Eiriksson
Eirikur Eiriksson
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15062 Visits: 18597
Couldn't post this solution at the time because of a quirk in the forum, had to PM it.
Cool
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
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyCommit int not null
)

create table #ItemLedgerEntry
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyRemain int 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

kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 479
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.
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 479
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?
kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 479
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
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyCommit int not null
)

create table #ItemLedgerEntry
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyRemain int 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]




mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 3317
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];


kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 479
Thanks for the suggestion Mickey but even with the test data I only get two results back.
mickyT
mickyT
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1576 Visits: 3317
That's interesting

From this
drop table #salesline;
drop table #ItemLedgerEntry;

--create the temp table
Create table #SalesLine
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyCommit int not null
)

create table #ItemLedgerEntry
(
No varchar (50) not null
, LocationCode varchar (50) not null
, QtyRemain int 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)


kwoznica
kwoznica
SSC-Addicted
SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)SSC-Addicted (453 reputation)

Group: General Forum Members
Points: 453 Visits: 479
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search