选择日期在某个范围内的忽略空值的记录

Question

我只想要那些记录必须存在于特定日期之间的记录。例如，如果给定的日期在2013-7-1和2013-7-17之间，我在采购表中有一个日期为2013-6-5的条目，在销售表中有一个日期为2013-7-17的特定产品的条目。它应该忽略这些行，并只选择在所需日期范围内发生了销售和购买的那些行。我下面的查询不能正常工作。由于上面定义的情况除以零，所以会发生异常。请帮帮我。

SELECT        o.Name AS Owner, c.Name AS Company, SUM(sq.PQuantity) - SUM(sq.SQuantity) AS Quantity, SUM(sq.PQuantity * sq.PRate) / SUM(sq.PQuantity) AS Rate, 
                             (SUM(sq.PQuantity) - SUM(sq.SQuantity)) * (SUM(sq.PQuantity * sq.PRate) / SUM(sq.PQuantity)) AS Amount,sq.CompanyId AS CompanyId, o.OwnerId AS OwnerId
    FROM            (SELECT        OwnerId, CompanyId, Quantity AS PQuantity, RatePerShare AS PRate, 0 AS SQuantity, 0 AS SRate, Date
                              FROM            Purchase
                              UNION ALL
                              SELECT        OwnerId, CompanyId, Quantity AS PQuantity, 0 AS PRate, 0 AS SQuantity, 0 AS SRate, Date
                              FROM            Bonus
                              UNION ALL
                              SELECT        OwnerId, CompanyId, Quantity AS PQuantity, CostOfShare AS PRate, 0 AS SQuantity, 0 AS SRate, Date
                              FROM            RightShare
                              UNION ALL
                              SELECT        OwnerId, CompanyId, 0 AS PQuantity, 0 AS PRate, Quantity AS SQuantity, RatePerShare AS SRate, Date
                              FROM            Sales) AS sq INNER JOIN
                             Owner AS o ON sq.OwnerId = o.OwnerId INNER JOIN
                             Company AS c ON sq.CompanyId = c.CompanyId
    WHERE        (sq.OwnerId = @Param1) AND (CONVERT(DATE, sq.Date, 111) BETWEEN @Param2 AND  @Param3)
    GROUP BY sq.CompanyId, c.Name, o.Name, o.OwnerId

Answer 1

将HAVING SUM(sq.PQuantity) > 0添加到查询的底部。