CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2516394
user2516394

Reputation: 81

Select records ignoring null values with date between some range

I want only those records where records must exist between particular dates. For e.g. if the date given is between 2013-7-1 AND 2013-7-17 and I've a entry in Purchase table dated: 2013-6-5 and a entry in Sale table dated 2013-7-17 for a particular product. It should ignore these rows and select only those rows where sale & purchase have occur in desired date range. My below query is not working properly. Because of the above defined situation divided by zero exception occurs. Kindly help me.

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

Upvotes: 0

Views: 384

Answers (1)

Anon
Anon

Reputation: 10908

Add HAVING SUM(sq.PQuantity) > 0 to the bottom of your query.

Upvotes: 1

Related Questions