November 21, 2013 at 6:57 am
Hi,
I have the following table
CREATE TABLE [dbo].[Orders](
[orderNo] [varchar](10) NOT NULL,
[orderLineNo] [int] NOT NULL,
[product] [varchar](10) NULL,
[orderQty] [int] NULL,
[receivedQty] [int] NULL,
[orderStatus] [varchar](10) NULL
) ON [PRIMARY]
GO
insert into Orders values ('A1000',1,'ABC100',10,10,NULL)
insert into Orders values ('A1000',2,'ABC100',15,15,NULL)
insert into Orders values ('A1000',3,'ABC101',17,17,NULL)
insert into Orders values ('A1001',1,'ABC101',5,4,NULL)
insert into Orders values ('A1001',7,'ABC103',50,1,NULL)
insert into Orders values ('A1001',8,'ABC104',25,25,NULL)
insert into Orders values ('A1002',1,'ABC104',24,23,NULL)
insert into Orders values ('A1002',2,'ABC100',15,15,NULL)
insert into Orders values ('A1003',1,'ABC105',50,0,NULL)
I want to update the field orderStatus to the following:
Orderstatus set to "Complete" when all of the orderNo has no outstanding Products so for this instance OrderNo A1000 will be the only one.
Orderstatus set to "Some" when some of the orderNo still has outstanding Products available instance OrderNo's A1001, A1002 will be the only ones.
Orderstatus set to "None" when none of the orderNo has received any products so for this instance OrderNo A1003 will be the only one.
Thanks
November 21, 2013 at 7:13 am
great job providing the DDL!
i'm assuming the key indicator here is the OrderQty - ReceivedQty columns to determine the status.
this seems to work for me in limited testing:
select * ,
orderStatus = CASE
WHEN OrderQty - ReceivedQty = 0
THEN 'Complete'
WHEN ReceivedQty = 0
THEN 'None'
WHEN OrderQty - ReceivedQty > 0
THEN 'Some'
ELSE 'Missing Test'
END
FROM Orders MyTarget
/*
UPDATE MyTarget
SET orderStatus = CASE
WHEN OrderQty - ReceivedQty = 0
THEN 'Complete'
WHEN ReceivedQty = 0
THEN 'None'
WHEN OrderQty - ReceivedQty > 0
THEN 'Some'
ELSE 'Missing Test'
END
FROM Orders MyTarget
*/
Lowell
November 21, 2013 at 7:20 am
My attempt:
update Orders
set OrderStatus = 'Complete'
WHERE orderNo IN ( select orderNo
from orders
group by orderno
having sum(OrderQty) - sum(receivedQty) = 0)
update Orders
set OrderStatus = 'Some'
WHERE orderNo IN ( select orderNo
from orders
group by orderno
having sum(OrderQty) - sum(receivedQty) > 0 and SUM(receivedQty) <> 0)
update Orders
set OrderStatus = 'None'
WHERE orderNo IN ( select orderNo
from orders
group by orderno
having sum(OrderQty) - sum(receivedQty) > 0 and SUM(receivedQty) = 0)
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 21, 2013 at 7:23 am
Thanks for this so far, it doesn't seem to have worked though? For orderNo "A1001" orderLineNo "8" I would expect that to show as "Some" because the whole orderNo is not complete. Same situation with orderNo "A1002" orderLineNo "8" and "2" I would expect to see Both these to show as "Some" because the whole order is not complete.
November 21, 2013 at 7:25 am
Abu Dina you must have replied when i was replying to the first response, it looks like your script works! Thanks
November 21, 2013 at 7:25 am
SQL_Kills (11/21/2013)
Hi,I have the following table
CREATE TABLE [dbo].[Orders](
[orderNo] [varchar](10) NOT NULL,
[orderLineNo] [int] NOT NULL,
[product] [varchar](10) NULL,
[orderQty] [int] NULL,
[receivedQty] [int] NULL,
[orderStatus] [varchar](10) NULL
) ON [PRIMARY]
GO
insert into Orders values ('A1000',1,'ABC100',10,10,NULL)
insert into Orders values ('A1000',2,'ABC100',15,15,NULL)
insert into Orders values ('A1000',3,'ABC101',17,17,NULL)
insert into Orders values ('A1001',1,'ABC101',5,4,NULL)
insert into Orders values ('A1001',7,'ABC103',50,1,NULL)
insert into Orders values ('A1001',8,'ABC104',25,25,NULL)
insert into Orders values ('A1002',1,'ABC104',24,23,NULL)
insert into Orders values ('A1002',2,'ABC100',15,15,NULL)
insert into Orders values ('A1003',1,'ABC105',50,0,NULL)
I want to update the field orderStatus to the following:
Orderstatus set to "Complete" when all of the orderNo has no outstanding Products so for this instance OrderNo A1000 will be the only one.
Orderstatus set to "Some" when some of the orderNo still has outstanding Products available instance OrderNo's A1001, A1002 will be the only ones.
Orderstatus set to "None" when none of the orderNo has received any products so for this instance OrderNo A1003 will be the only one.
Thanks
Hi Lowell,
Not sure this is going to work for order number A1002
Thanks.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 21, 2013 at 7:29 am
Abu Dina (11/21/2013)
Hi Lowell,
Not sure this is going to work for order number A1002
Thanks.
you are right; after i saw your post, i realized i misread the requirement and that he wanted the status on a per order basis, and not a per LINE basis that was my post trying to handle.
Lowell
November 21, 2013 at 7:30 am
Three updates looks a bit ugly though... I'm trying to figure out how to do this with one update.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 21, 2013 at 7:41 am
SQL_Kills (11/21/2013)
Abu Dina you must have replied when i was replying to the first response, it looks like your script works! Thanks
You're welcome!
Perhaps there is no need for three updates
UPDATE a
SET a.OrderStatus = b.OrderStatus
FROM orders AS a
INNER JOIN (select orderNo,
CASE
WHEN SUM(OrderQty) - SUM(receivedQty) = 0 THEN 'Complete'
WHEN SUM(OrderQty) - SUM(receivedQty) > 0 and SUM(receivedQty) <> 0 THEN 'Some'
ELSE 'None'
END AS OrderStatus
FROM orders
GROUP BY orderno) AS b
ON a.OrderNo = b.OrderNo
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
November 21, 2013 at 8:42 am
This is basically the same that the previous update posted by Abu Dina.
UPDATE o SET
orderStatus = CASE WHEN s.orderQty = s.receivedQty THEN 'Complete'
WHEN s.orderQty > s.receivedQty AND s.receivedQty > 0 THEN 'Some'
ELSE 'None' END
FROM Orders o
JOIN (SELECT orderNo,
SUM( orderQty) orderQty,
SUM( receivedQty) receivedQty
FROM Orders
GROUP BY orderNo) s ON o.orderNo = s.orderNo
November 21, 2013 at 5:47 pm
If Order status is an attribute of an Order, why does it appear on each order detail line?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 22, 2013 at 4:35 pm
Sorry not sure what u mean?
November 22, 2013 at 5:22 pm
dwain.c (11/21/2013)
If Order status is an attribute of an Order, why does it appear on each order detail line?
Order status appears on each order detail line, hence when a status changes you must change multiple rows to the new status, running the risk that the order status gets out of sync.
Now, if the order status were on say the header record for the order. then the data would only need update in one place when an order's status is changed.
Hence status is an attribute of an order's header and not its detail (although there could be a separate status for a detail line indicating whether that detail line was completely filled).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 23, 2013 at 4:05 am
So what do expect me to do instead? As not completely sure what to do now? Thanks
November 23, 2013 at 4:57 pm
You should have a table for Orders and another one for Orders details.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply