October 16, 2008 at 12:45 pm
Hi Forum,
I'm trying to create a view which will show me a summary of what has been packed so far on a group of orders. Here's a sample of code to illustrate the problem
Drop table Groupheader
Drop table OrderHeader
Drop table OrderDetail
Drop table GroupCartons
go
Create Table GroupHeader (GroupNo int)
Create Table OrderHeader (OrderNo int, GroupNo int)
Create Table OrderDetail (OrderNo int, ItemNo int, OrderedQty int)
Create Table GroupCartons (GroupNo int, ItemNo int, PackedQty int)
go
insert into GroupHeader (GroupNo)
Select 1 union all
Select 2
Insert into OrderHeader (OrderNo, GroupNo)
Select 1, 1 union all
Select 2, 1
Insert into OrderDetail (OrderNo, ItemNo, OrderedQty)
Select 1, 1, 10 union all
Select 1, 2, 10 union all
select 2, 1, 10 union all
select 2, 2, 10
Insert into GroupCartons (GroupNo, ItemNo, PackedQty)
select 1, 1, 1
Go
selecta.groupno, sum(b.orderedqty) OrderedQty,
sum(c.PackedQty) PackedQty, b.itemno
fromGroupHeader a
join
OrderHeader d
on
d.GroupNo = a.Groupno
join
OrderDetail b
on
b.Orderno = d.OrderNo
left outer join
GroupCartons c
on
c.ItemNo = b.ItemNo and c.groupno = a.groupno
group by
a.groupno, b.itemno, c.itemno
When I execute this code I get the following result.
groupno OrderedQty PackedQty itemno
----------- ----------- ----------- -----------
1 20 2 1
1 20 NULL 2
Warning: Null value is eliminated by an aggregate or other SET operation.
(2 row(s) affected)
I don't understand this. I only have one record with a quantity of 1 in my GroupCartons table.
select * from groupcartons
GroupNo ItemNo PackedQty
----------- ----------- -----------
1 1 1
(1 row(s) affected)
Why is my PackedQty calculated twice ? I realize it's because I have two OrderDetail lines with the same ItemNo but shouldn't it be summed based on the Group By clause and show me only 1 ?
The results I need is
groupno OrderedQty PackedQty itemno
----------- ----------- ----------- -----------
1 20 1 1
1 20 NULL 2
How can I create a view that will properly sum() my PackedQty ? This is only some sample code that illustrates my problem. I have to do this on tables that are part of a 3rd party system, I can't change the structures of the tables and OrderDetail doesn't have GroupNo. The only way to join GroupCartons with OrderDetail is via OrderHeader.
October 16, 2008 at 1:02 pm
You're getting the PackedQty = 2, because there are 2 records in OrderHeader where GroupNo = 1, and each of those has a record in the OrderDetail table with ItemNo = 1. Since you're joining to GroupCartons by GroupNo and ItemNo, there will be 2 records for the SUM() to add up, even though they originated from the same record.
Below is what the resulting data looks like with your current joins and without the SUM and GROUP BY:
groupnoOrderNoItemNoPackedQty
1111
112NULL
1211
122NULL
If GroupCartons is supposed to be a child table of OrderDetail, it will need OrderNo and ItemNo in it, If it is supposed to be a child table of GroupHeader directly, then it doesn't make sense for ItemNo to be in it.
How does GroupCartons relate to the other 3 tables?
October 16, 2008 at 1:12 pm
Chris Harshman (10/16/2008)
How does GroupCartons relate to the other 3 tables?
Some of our customers sends us orders regularly, sometimes as many as 2 or 3 within a week. They basically re-order at the end of each day if they sold some of our products.
In order to reduce the number of boxes and save on shipping costs, those orders have to be packed and shipped together. Our packing system does that by grouping the orders.
Our packers do not pack orders, they pack Groups and as they scan the barcodes on the items, they need to see on the screen how many are scanned so far vs the total that is required for the group of orders.
There is no relation between GroupCartons and the Orders, it is only a child of GroupHeader that shows what has been packed for that group of order.
In the example I've set here, the packer should see on his screen the he needs to pack 20 units of Item # 1, that he's packed 1 so far and that he has 19 left (the 19 is calculated and displayed in real time as OrderedQty - PackedQty).
The view I'm trying to create is intented to display that information to the packer, that's why I need to add up all the OrderedQty by ItemNo and all the PackedQty for that same ItemNo.
October 16, 2008 at 1:30 pm
OK, maybe I misunderstood ItemNo before. The problem is you'll need to SUM each of those values separately since they are not related. Maybe something with subqueries like:
select gh.GroupNo, ord.OrderedQty,
c.PackedQty, ord.ItemNo
from GroupHeader gh
inner join
(select oh.GroupNo, od.ItemNo, sum(od.OrderedQty) as OrderedQty
from OrderHeader oh
inner join OrderDetail od on oh.OrderNo = od.OrderNo
group by oh.GroupNo, od.ItemNo) ord ON gh.GroupNo = ord.GroupNo
left outer join
(select GroupNo, ItemNo, SUM(PackedQty) AS PackedQty
from GroupCartons
group by GroupNo, ItemNo) c on gh.GroupNo = c.GroupNo and ord.ItemNo = c.ItemNo
October 16, 2008 at 1:40 pm
Thanks a lot Chris. That works perfectly on my test script, I'm gonna have to try it on the real database but it should work just the same.
I think I need to spend more time studying "inner join" because I would have never thought of that.
I found another solution a few minutes ago but it's surely not as elegant as yours, I just created another view to summarize my OrderDetail lines by GroupNo and replaced OrderDetail with that view on my final select statement.
Create View vOrderSummary as
Select a.groupno, b.itemno, sum(b.OrderedQty) OrderedQty
from OrderHeader a, Orderdetail b
where b.OrderNo = a.OrderNo
Group by a.groupno, b.itemno
Go
selecta.groupno, sum(b.orderedqty) OrderedQty,
sum(c.PackedQty) PackedQty, b.itemno
fromGroupHeader a
join
vOrderSummary b
on
b.GroupNo = a.GroupNo
left outer join
GroupCartons c
on
c.ItemNo = b.ItemNo and c.groupno = b.groupno
group by
a.groupno, b.itemno, b.groupno
I'm very grateful for your help because I really hate my solution, I've never been a big admirer of views based on other views.
October 16, 2008 at 2:05 pm
Views based on other views shouldn't be too much of a problem, the database engine typcially is able to resolve a query with a view and a derived table subquery like my example the same way. The key point here was the need to calculate the SUM(OrderedQty) and SUM(PackedQty) separately instead of at the same level of the query. Both our methods accomplish this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply