Can this be coded better?

  • 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

  • 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

  • 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

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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

  • This is great, SSCrazy. It is exactly what I am looking for.

    Short, simple, easy to read, thus easy to maintain.

    Thank you!

  • 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.

  • 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    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