CODEWITHSUNDEEP
sql-serversql-server-2008sql-server-2012
Love_Egypt
Love_Egypt

Reputation: 189

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

I have this sql database

enter image description here

And I want to show my data such as following:

enter image description here

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

Upvotes: 0

Views: 81

Answers (1)

Sandr
Sandr

Reputation: 776

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

Upvotes: 1

Related Questions