How to get all order ID which are not paid in SQL Server 2008?

[Doudy2013]

Hi , I want to get all order id numbers for selected customer which not payed till now, my data show as following:

What I want is Write a SELECT statement that answers this question:

select orderID from order where customer id = @custID and Total cashmovementValue for current order id is less than total (sold quantity * salePrice ) for current order id.

How to do it?

Thanks.

[r035198x]

1.) I can't view your picture from where I am.
2.) This is more an SQL question than a C# question.
3.) The query to write depends on your table structure. e.g are the column cashmovementValue, sold quantity and salePrice all on the order table?

[Rabbit]

Please use code tags when posting code. Your thread has been moved to the SQL Server forum.

[swathee]

SELECT O.OrderID
FROM [Order] O
INNER JOIN (
   -- Add up cost per order
SELECT OrderID,
SUM(SoldQuantity * P.SalePrice) AS Total
FROM OrderLine
INNER JOIN Product P ON P.ProductID = OrderLine.ProductID     
GROUP BY OrderID
) OL ON OL.OrderID = O.OrderID
LEFT JOIN (
    -- Add up total amount paid per order
SELECT OrderID,
SUM(CashMovementValue) AS Total
FROM CashMovement
GROUP BY OrderID
) C ON C.OrderID = O.OrderID
WHERE O.CustomerID = @custID
AND ( C.OrderID IS NULL OR C.Total < OL.Total )