如何在sql server中获取此报告的查询？ [英] How can I get a query for this report in sql server?

问题描述

我有这个sql数据库



http://i.stack.imgur.com/NCL4T.jpg [ ^ ]



我想显示以下数据：



http：//i.stack。 imgur.com/KX6KS.jpg [ ^ ]



注：



总销售额=总和（ProductsTbl.SalePrice * OrderDetailsTbl.SoldQuantity）



折扣=总和（ProductsTbl.SalePrice * OrderDetailsTbl.SoldQuantity）* DiscountRatesTbl.DiscountRate / 100



支付= SUM（isnull（CashMovementTbl.CashMovementValue，00.00）



我尝试了这个查询但未能获得折扣：

I have this sql database

http://i.stack.imgur.com/NCL4T.jpg[^]

And I want to show my data such as following:

http://i.stack.imgur.com/KX6KS.jpg[^]

Note:

Total sales = Sum(ProductsTbl.SalePrice * OrderDetailsTbl.SoldQuantity)

Discounts = Sum(ProductsTbl.SalePrice * OrderDetailsTbl.SoldQuantity) * DiscountRatesTbl.DiscountRate/100

Payed = SUM(isnull(CashMovementTbl.CashMovementValue,00.00)

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

那么如何在SQL Server中进行查询以显示这样的数据。



感谢您的帮助。

So how can I do a query in SQL Server to show data like that.

Thanks for any help.

推荐答案

您的错误在

Select isnull(sum(p.SalePrice * d.SoldQuantity)* dr.DiscountRate/100),00.00

关闭括号 isnull 不包括折扣为空时的默认值。



这应该类似于

The closing bracket on isnull is not including the default value for when the discount is null.

This should probably be something like

Select (sum(p.SalePrice * d.SoldQuantity)* isnull(dr.DiscountRate/100, 0))

这篇关于如何在sql server中获取此报告的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！