August 28, 2015 at 11:14 am
Here is my requirement
Table 1 Order
order ID, Sales order ID order date, order type
Table 2 Order details
Order Details ID, Order ID, Order Stage
Table 3 Related Order details
Order ID(FK to Order ID), Related Order Details ID(FK to Order Details ID), Related Order ID( FK to Order ID)
Here is example
Table 1 Order
1, 1234, 2015-01-01, Refill
2, 1234, 2015-02-02, Extension
Table 2 Order Details
1, 1, Approved
2, 1, Approved
3, 2, Rejected
Table 3 Related Order Details
2, 1, 1
2, 2, 1
i have to Select Order, Order Details and Order Status
Order Status is determined from Order Stage as follows
If, at least one order detail line(from Order Details and Related Order details table) is approved, that Order status=Approved.
For the example, Order Status of Order ID=2, is Approved based on order status for order details lines 3(from table 2) and order details ID 1 and 2 (from table 3)
How to combined order stage from table 2 and table 3 and then compute order status
August 28, 2015 at 1:05 pm
Assuming you know how to write the sql to get all the details for the order and details for any related orders this is actually pretty simple.
case when max(case when OrderDetails.OrderStage = 'Approved' then 1 else 0 end) = 1 then 'Approved' else 'Rejected' end as NewOrderStatus
It would be better if you had the order status in a lookup table instead of storing the strings. That would make a lot of things simpler in the long run.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 28, 2015 at 1:51 pm
Thank you for your response.
case max on OrderDetails.OrderStage, will work.
But i am unable to join order details formation from OrderDetails and RelatedOrderDetails tables
case max should take into account Order details rows in OrderDetails and RelatedOrderDetails
In my example Order ID =1 does not have RelatedOrderDetails but Order ID =2 has RelatedOrderDetails, how to determine Order Status based on data in OrderDetails and RelatedOrderDetails tables
August 28, 2015 at 1:56 pm
Do you need a select?
I started to work on an update, but it might give you an idea.
CREATE TABLE #Order(
OrderID int CONSTRAINT PK_OrderID PRIMARY KEY,
SalesOrderID int,
OrderDate date,
OrderType varchar(20),
OrderStatus varchar(20)
);
CREATE TABLE #OrderDetails(
OrderDetailsID int CONSTRAINT PK_OrderDetailsID PRIMARY KEY,
OrderID int,
OrderStage varchar(20)
);
CREATE TABLE #RelatedOrderDetails(
OrderID int,--(FK to Order ID),
RelatedOrderDetailsID int,--(FK to Order Details ID),
RelatedOrderID int,--( FK to Order ID)
CONSTRAINT FK_OrderID FOREIGN KEY ( OrderID) REFERENCES #Order(OrderID),
CONSTRAINT FK_OrderDetailsID FOREIGN KEY ( RelatedOrderDetailsID) REFERENCES #OrderDetails(OrderDetailsID),
CONSTRAINT FK_RelatedOrderID FOREIGN KEY ( RelatedOrderID) REFERENCES #Order(OrderID)
);
INSERT INTO #Order(
OrderID ,
SalesOrderID,
OrderDate ,
OrderType
)
VALUES
(1, 1234, '2015-01-01', 'Refill'),
(2, 1234, '2015-02-02', 'Extension');
INSERT INTO #OrderDetails
VALUES
(1, 1, 'Approved'),
(2, 1, 'Approved'),
(3, 2, 'Rejected');
INSERT INTO #RelatedOrderDetails
VALUES
(2, 1, 1),
(2, 2, 1);
--Option 1
UPDATE o SET
OrderStatus = 'Approved'
FROM #Order o
JOIN #OrderDetails d ON o.OrderID = d.OrderID
LEFT
JOIN #RelatedOrderDetails r ON d.OrderID = r.OrderID
LEFT
JOIN #OrderDetails d2 ON d2.OrderID = r.RelatedOrderID
AND d2.OrderDetailsID = r.RelatedOrderDetailsID
WHERE d.OrderStage = 'Approved'
OR d2.OrderStage = 'Approved'
--Option 2
UPDATE o SET
OrderStatus = 'Approved'
FROM #Order o
WHERE EXISTS(
SELECT *
FROM #OrderDetails d
LEFT
JOIN #RelatedOrderDetails r ON d.OrderID = r.OrderID
LEFT
JOIN #OrderDetails d2 ON d2.OrderID = r.RelatedOrderID
AND d2.OrderDetailsID = r.RelatedOrderDetailsID
WHERE (d.OrderStage = 'Approved' OR d2.OrderStage = 'Approved')
AND o.OrderID = d.OrderID)
SELECT * FROM #Order;
GO
DROP TABLE #RelatedOrderDetails;
DROP TABLE #Order;
DROP TABLE #OrderDetails;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply