Q:

How can I get a query for this report in sql server?

I have this sql database

And I want to show my data such as following:

Note:

Total sales = Sum(ProductsTbl.SalePrice *
  OrderDetailsTbl.SoldQuantity)
Discounts = Sum(ProductsTbl.SalePrice * OrderDetailsTbl.SoldQuantity)
  * DiscountRatesTbl.DiscountRate/100
Payed = SUM(isnull(CashMovementTbl.CashMovementValue,00.00)

So how can I do a query in SQL Server to show data like that.
Thanks for any help.
I tried this query but failed to got Discounts:
SELECT  c.CustName as 'Customer' ,

Total Sales= ( SELECT SUM(p.SalePrice * d.SoldQuantity) FROM OrderDetailsTbl d 
INNER JOIN  OrdersTbl o ON d.orderID = o.orderID INNER JOIN  ProductsTbl p ON p.ProductID = d.ProductID
WHERE o.CustomerID = c.CustomerID ) ,

Discounts= (Select isnull(sum(p.SalePrice * d.SoldQuantity)* dr.DiscountRate/100),00.00 FROM OrderDetailsTbl d 
INNER JOIN  OrdersTbl o ON d.orderID = o.orderID INNER JOIN  ProductsTbl p ON p.ProductID = 
d.ProductID inner join DiscountRatesTbl dr on dr.orderID = o.orderID WHERE o.CustomerID = c.CustomerID ) ,

Payed=  ( SELECT  SUM(isnull(cm.CashMovementValue,00.00)) FROM CashMovementTbl cm
INNER JOIN OrdersTbl o  ON o.orderID = cm.orderID WHERE  o.CustomerID = c.CustomerID)

FROM    CustomersTbl c
where ( SELECT SUM(p.SalePrice * d.SoldQuantity) FROM OrderDetailsTbl d 
INNER JOIN  OrdersTbl o ON d.orderID = o.orderID INNER JOIN  ProductsTbl p ON p.ProductID = 
d.ProductID WHERE o.CustomerID = c.CustomerID ) is not null 
order by c.CustName

A:

This is not an optimal way for query writing...
But replace the part of your query for discount calculation with the following:
Discounts= (Select isnull(SUM(p.SalePrice * d.SoldQuantity* dr.DiscountRate/100),00.00) FROM OrderDetailsTbl d 
INNER JOIN  OrdersTbl o ON d.orderID = o.orderID INNER JOIN  ProductsTbl p ON p.ProductID = 
d.ProductID inner join DiscountRatesTbl dr on dr.orderID = o.orderID WHERE o.CustomerID = c.CustomerID ) ,

This will works.
Simply the SUM must include all multipliers and not simple multiplication of p.SalePrice and d.SoldQuantity

