I'm trying to write a function for SQL Sever that will calculate the total price of an order. This is easily said and done, but the problem is that instead of the order having multiple lines for each item, it uses a UnitsOrdered field that stores the quantity.

The image below shows the three line items of an order and the fields I'm discussing.

The outcome I'm looking for is the following:

Creating a query that can get the total of lines with more than 1 item has been my challenge.

The below examples are different pieces of code that I tried.

Alter Function fn_CalculatePTPrice
  (@psPickTicket   TPickTicketNo)
  -------------------------------
  Returns TFloat
As 
Begin
  Declare
    @ftotalPrice       TFloat;
    @iReturn           TFloat;

    SELECT @fTotalPrice = ISNULL((
      SELECT SUM(P.RetailUnitPrice * P.UnitsOrdered)
      from tblPickTicketDtl P
      join tblCase C on (P.PickTicketNo = C.PickTicketNo)
      where P.PickTicketNo = @psPickTcket
      ), 0);

    set @iReturn = @fTotalPrice;
    _Return:
  Return(@iReturn);
End /* fn_CalculatePTPrice */

and this

ALTER Function fn_CalculatePTPrice
  (@psPickTicket   TPickTicketNo)
  -------------------------------
  Returns TFloat
As 
Begin
  Declare
    @iReturn           TFloat,
    @iTotalLineNumbers TInteger,
    @iIndex            TInteger,
    @fTotalPrice       TFloat;

    set @iIndex = 1;
    set @iTotalLineNumbers = (ISNULL((select top 1 PickLineNo
                             from tblPickTicketDtl
                             where PickTicketNo = @psPickTicket
                             order by PickLineNo desc), 0))

    while(@iIndex <= @iTotalLineNumbers)
      BEGIN

        set @fTotalPrice  = (ISNULL((select  SUM(P.RetailUnitPrice*P.UnitsOrdered)
                        from tblPickTicketDtl P
                        left outer join tblCase C on (P.PickTicketNo = C.PickTicketNo)
                        where P.PickTicketNo = @psPickTicket
                        and P.PickLineNo = @iIndex), 0))
        set @iIndex  = 1;
      END

    set @iReturn = @fTotalPrice;

    _Return:
  Return(@iReturn);
End /* fn_CalculatePTPrice */

At this point I'm not really sure what to try next, any help would be appreciated.

UPDATE:

My company uses User-Defined Data Types in our database, so TInteger = Integer, TFloat = Float, and TPickTicketNo = varchar(30)

CodePudding user response：

Sample data:

CREATE TABLE orders (OrderID int, RetailUnitPrice float, UnitsOrdered int);

INSERT INTO orders (OrderID,RetailUnitPrice,UnitsOrdered)
VALUES (1,485.41,1),(1,252.3,1),(1,25.91,2),(2,485.41*0.5,1),(2,252.3*0.75,1),(2,25.91*4,2);

Return line items and order totals in-line:

SELECT OrderID, RetailUnitPrice, UnitsOrdered, SUM(RetailUnitPrice*UnitsOrdered) OVER (PARTITION BY OrderID) AS OrderTotal
FROM orders;

Return line items and order sub-totals:

SELECT OrderID
, CASE WHEN GROUPING(RetailUnitPrice) = 1 THEN 'Total for OrderID '   CAST(OrderID AS nvarchar(10)) ELSE CAST(RetailUnitPrice AS nvarchar(10)) END AS RetailUnitPrice
, CASE WHEN GROUPING(UnitsOrdered) = 1 THEN 'Total for OrderID '   CAST(OrderID AS nvarchar(10)) ELSE CAST(UnitsOrdered AS nvarchar(10)) END AS UnitsOrdered
, SUM(RetailUnitPrice*UnitsOrdered) AS Total
FROM orders
GROUP BY GROUPING SETS ((OrderId,RetailUnitPrice,UnitsOrdered),(OrderId))

dbfiddle.uk

CodePudding user response：

I found a better way of handling this issue.

I'm going to use the following:

select SUM(TotalPrice)
From (select SUM(UnitsOrdered * RetailUnitPrice) as TotalPrice
from tblPickTicketDtl
where PickTicketNo = @psPickTicket) as tblAnswer

This gives me the result I'm looking for. I'm not a pro at SQL so if you see any improvements please let me know.