From r In ReceiptLines
Where
r.RECEIPT.RECEIPTDATE >= _reportStartDate
And r.RECEIPT.RECEIPTDATE <= _reportEndDate
Let amount = r.QUANTITY * r.PRICE
Let discount = r.RECEIPTDISCOUNTs.Sum(Function(d) d.DISCOUNT)
where discount > 0
Group By Department = r.ITEMSTYLE.ITEM.CATEGORY.DEPARTMENT.DEPARTMENTNAME
Into Sales = Sum(amount - discount),
Average = Average(amount - discount),
Count = Count()
我正在从ReceiptLine、Receipt和ReceiptDiscount表中获取所有部门及其销售额、平均值和数量。我遇到的问题是,如果我删除where discount > 0,就会出现空异常。但是如果我包括它,那么我只会得到有折扣的销售额。如何编写查询以获得所有销售额减去折扣(如果有的话)。非常感谢您的帮助。