July 3, 2013 at 9:45 pm
My goal is to get a dataset of orders with their overall statuses.
The order overall status is a computed column based of their detail line statuses.
I have 2 options:
1. Create a view of distinct orders and their overall statuses via a function call
2. Create a computed column on the order table ( non persisted) because the order status changes over time.
This computed column will still be the result of a function call.
I am choosing option 1. Attached is sample data and function code.
I am looking for suggestions and ideas on how to make the function code better. So far I only code a portion of the function. There are other rules to implement in the function so I am trying to find ways to make it as readable, short and simple.
Eventually my function will return an INT. I just return varchar(50) for now to facilitate verification.
Thank you!
CREATE TABLE dbo.OrderDetail
( OrderID INT NOT NULL
,OrderDetailID INT NOT NULL
, ProductID INT NOT NULL
, Qty INT NOT NULL
, STATUS varchar(50) NOT NULL
)
-- OrderID=1 overall status = 'SHIPPED'
INSERT INTO OrderDetail values ( 1, 1,100, 10, 'Shipped')
INSERT INTO OrderDetail values ( 1, 2, 200, 10, 'Shipped')
-- OrderID=2 overall status = 'BACKORDERED'
INSERT INTO OrderDetail values ( 2, 3,100, 10, 'Shipped')
INSERT INTO OrderDetail values ( 2, 4, 200, 10, 'Backordered')
-- OrderID=3 overall status = 'CANCELLED'
INSERT INTO OrderDetail values (3, 5,100, 10, 'Cancelled')
INSERT INTO OrderDetail values ( 3, 6, 200, 10, 'Cancelled')
-- OrderID=4 overall status = 'SHIPPED'
INSERT INTO OrderDetail values (4, 7,100, 10, 'Cancelled')
INSERT INTO OrderDetail values ( 4, 8, 200, 10, 'Shipped')
INSERT INTO OrderDetail values ( 4, 9, 300, 10, 'Shipped')
-- OrderID=5 overall status = 'NO STATUS'
INSERT INTO OrderDetail values (5, 10,100, 10, 'No status')
INSERT INTO OrderDetail values ( 5, 11, 200, 10, 'No status')
INSERT INTO OrderDetail values ( 5,12, 300, 10, 'No status')
USE [TrainingDB]
GO
CREATE FUNCTION [dbo].[udf_CalculateOrderStatus]
(
@OrderID int
)
RETURNS varchar(50)
AS
BEGIN
DECLARE @status varchar(50)
DECLARE @TotalDetails int
SET @status=NULL
SELECT @TotalDetails = Count(Status) FROM [dbo].[OrderDetail] where Orderid=@OrderID
-- SHIPPED?
IF @status is null
BEGIN
SELECT @status= 'SHIPPED'
FROM [dbo].[OrderDetail]
Where OrderID=@OrderID
GROUP BY OrderID
HAVING MAX(Status) = 'Shipped' AND
MIN(Status) = 'Shipped'
SELECT @status= 'SHIPPED'
FROM [dbo].[OrderDetail]
Where OrderID=@OrderID
GROUP BY OrderID
HAVING (COUNT(CASE Status WHEN 'Shipped' THEN 1 END)) >= 1
AND (COUNT(CASE Status WHEN 'Shipped' THEN 1
WHEN 'Cancelled' THEN 1 END )) = @TotalDetails
END
-- CANCELLED?
IF @status is null
BEGIN
SELECT @status= 'CANCELLED'
FROM [dbo].[OrderDetail]
Where OrderID=@OrderID
GROUP BY OrderID
HAVING MAX(Status) = 'Cancelled' AND
MIN(Status) = 'Cancelled'
END
--BACKORDERED?
IF @status is null
BEGIN
SELECT @status= 'BACKORDERED'
FROM [dbo].[OrderDetail]
Where OrderID=@OrderID
GROUP BY OrderID
HAVING (COUNT(CASE Status WHEN 'BackOrdered' THEN 1 END )) >= 1
END
--NO STATUS?
IF @status is null
BEGIN
SELECT @status= 'NO STATUS'
FROM [dbo].[OrderDetail]
Where OrderID=@OrderID
GROUP BY OrderID
HAVING MAX(Status) = 'No Status' AND
MIN(Status) = 'No Status'
END
RETURN @status
END
GO
-- Goal: Set of Orders with their overall statuses
select distinct orderid, dbo.udf_CalculateOrderStatus(orderid) as OrderShipmentStatus from dbo.orderdetail
July 3, 2013 at 11:05 pm
What was the Status Priority ?
say if an order details has
BACKORDERED
CANCELLED
SHIPPED
NO STATUS ., wat should be the overall ?
need some thing like
StatusPriorityStatusDesc
1NO STATUS
2CANCELLED
3BACKORDERED
4SHIPPED
July 3, 2013 at 11:18 pm
What was the logic for order 2 with all cancelled in detail , but overall as shipped
;WITH CTEStatusPriority (StatusPriority,StatusDesc)
AS
(
SELECT '1','NO STATUS' UNION
SELECT '2','CANCELLED' UNION
SELECT '3','BACKORDERED' UNION
SELECT '4','SHIPPED'
),
CTEOverallOrder
AS(
SELECT OrderID, MIN(StatusPriority) as MINStatusPriority
FROM dbo.orderdetail
JOIN CTEStatusPriority
ON StatusDesc = Status
GROUP BY OrderID )
SELECT OrderID , StatusDesc FROM CTEOverallOrder
JOIN CTEStatusPriority
ON MINStatusPriority = StatusPriority
SOMTHING LIKE ABOVE
July 3, 2013 at 11:52 pm
Coriolan,
The following matches your results by using a cross tab to generate counts of order detail lines by status.
WITH CountOfStatus AS (
SELECT ORderID
,Shipped=COUNT(CASE [Status] WHEN 'Shipped' THEN 1 END)
,Backordered=COUNT(CASE [Status] WHEN 'Backordered' THEN 1 END)
,Cancelled=COUNT(CASE [Status] WHEN 'Cancelled' THEN 1 END)
,[No Status]=COUNT(CASE [Status] WHEN 'No Status' THEN 1 END)
,TotalDetails=COUNT(*)
FROM dbo.orderdetail
GROUP BY OrderID)
SELECT OrderID
,OverallStatus=CASE
WHEN Cancelled = TotalDetails THEN 'Cancelled'
WHEN BackOrdered > 0 THEN 'BackORdered'
WHEN Shipped + Cancelled = TotalDetails THEN 'Shipped'
WHEN [No Status] = TotalDetails THEN 'No Status'
ELSE NULL END
FROM CountOfStatus;
In the final CASE, I've matched up to your sample data but it may not fully cover all of your business rules for resolving cases where multiple different statuses are assigned to the line items, so you'll need to go through that to see how it needs to be modified.
This approach should be significantly faster than the SVF you've written to handle the rules.
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
July 4, 2013 at 8:49 am
This is great, SSCrazy. It is exactly what I am looking for.
Short, simple, easy to read, thus easy to maintain.
Thank you!
July 4, 2013 at 8:52 am
Hi Bala,
Thank you for the replies. Typically, the status names won't be used in the final version. The final version will use Status IDs. My sample data is coded with status names just for the sake of easy illustration to convey what my intentions are.
July 4, 2013 at 5:12 pm
Coriolan (7/4/2013)
This is great, SSCrazy. It is exactly what I am looking for.Short, simple, easy to read, thus easy to maintain.
Thank you!
You wouldn't be the first to call me crazy but the name is actually Dwain.C!
You're welcome.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply