April 24, 2013 at 9:52 pm
I have 2 tables ( orderheader and ordershipmentdetail). The order can be closed when all products and their requested quantities are received.
A product in a specific order can be shipped partially( for example, the customer orders 10 bulbs, first shipment sends out 6 bulbs, the second shipment 4 bulbs totally the requested quantity of 10).
This is sample data where the expected result is to set status=20 on headerid=4 because all its shipments are fulfilled. The other headers will have status=10.
Can you suggest how I can find the set of headerids that have all requested shipments fulfilled?
Thank you!
-- status 10 means open
-- status 20 means closed
create table #header
( headerid int,
status int)
-- status 1 means Requested
-- status 2 means Received
create table #details
(detailid int,
headerid int,
product int,
QuantityRequested int,
QuantityReceived int,
status int)
insert into #header
values (1,null),
(2,null),
(3,null),
(4,null)
insert into #details
values (10, 1, 1000, 100,50,2),
(11, 1, 1000, 100,25,2),
(12, 1, 1100, 8,8,2),
(13, 2, 5000, 100,25,2),
(14, 2, 5000, 100,75,2),
(15, 3, 6000, 12,null,1),
(16, 4, 7000, 12,6,2),
(17, 4, 7000, 12,6,2),
(18, 4, 8000, 15,5,2),
(19, 4, 8000, 15,5,2),
(20, 4, 8000, 15,5,2),
(21, 4, 9000, 3,3,2)
select * from #header
select * from #details
select headerid, QuantityRequested, sum(QuantityReceived) as QuantityReceived
,product, status
from #details d
group by headerid,QuantityRequested, product, status
drop table #details
drop table #header
April 24, 2013 at 10:38 pm
This is a little tricky, because you are putting the same data across multiple rows (quantity requested is in each row for the particular headerid and product). Because of this, you need a two steps process: 1) get the quantity received by headerid and product (without summing the quantity requested in each row) and 2) get the quantity received in total. Here is the query I put together using the data you provided. While trying to make the update look a little less complicated, I used a CTE (Common Table Expression, http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx):
WITH q
AS
(
select d.headerid, product, QuantityRequested, sum(QuantityReceived) as QuantityReceived
from #details d
join #header h on d.headerid = h.headerid
group by d.headerid, product, QuantityRequested
)
update h
set status = 20
from
(
select headerid, SUM(QuantityRequested) QuantityRequested, SUM(QuantityReceived) QuantityReceived
from q
group by headerid
) a
join #header h on a.headerid = h.headerid
where QuantityReceived = QuantityRequested
The CTE does the first sum (by headerid and product) and then the second gives us the total quantity requested to the customer. We make sure that total quantity received equals the total quantity requested.
--------
Want some simple query helps? Check out my blog:
http://lantztechknowledge.blogspot.com/
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
April 25, 2013 at 11:20 am
Thank you! That worked. Now my question is what if on the QuantityRequested is not stored on multiple rows? ( say it is stored only on 1 row, the other partial shipments of the product contain NULL in their rows? So we then can sum the Quantity requested ( putting 0 where it is null).
Will this simplify your query?
April 25, 2013 at 11:43 am
The best option would be to normalize your database. Have one table that has the general order information (total items ordered, etc.) and have another table that contains the shipment information (without total items ordered).
Check out this article for 1st Normal Form (1NF) and 2nd Normal Form (2NF):
http://databases.about.com/od/specificproducts/a/normalization.htm
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply