Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using a CTE to aggregate seperate tables Expand / Collapse
Author
Message
Posted Wednesday, August 20, 2014 3:23 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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
Post #1605620
Posted Monday, August 25, 2014 12:37 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 1,476, Visits: 2,777
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
Post #1607184
Posted Monday, August 25, 2014 2:02 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 2,419, Visits: 6,721
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
(
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
Post #1607233
Posted Monday, August 25, 2014 2:22 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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.
Post #1607239
Posted Monday, August 25, 2014 2:36 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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?

Post #1607248
Posted Monday, August 25, 2014 3:06 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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]



Post #1607258
Posted Monday, August 25, 2014 4:56 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:24 PM
Points: 1,081, Visits: 3,171
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];

Post #1607296
Posted Monday, August 25, 2014 6:57 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
Thanks for the suggestion Mickey but even with the test data I only get two results back.
Post #1607305
Posted Monday, August 25, 2014 7:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:24 PM
Points: 1,081, Visits: 3,171
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)

Post #1607312
Posted Monday, August 25, 2014 8:39 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 8:35 PM
Points: 187, Visits: 434
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!
Post #1607316
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse