Iteration in sql query

Question

hi everyione, i'm getting wrong result of query in group function , i'm using 3 tables 1. sale with columns AccointId,NetAmount , quantity, date 2. Purchase colums AccounId,NetAmount,quantity,date 3.Account colums AccountId,AccountName

i made a stored procedure that take two input like Date1,Date2

i'm required that Account.AccountName,Sum of NetAmount of Purchase - SUm of NetAmoount of Sale of Date below Date amd Sum of NetAmount of Purchase-Sum of NetAmouint of Sale of Date Between Date1 and Date2, Sum of NetAmount of Sale and Purchase of Date between Date1 and Date2

i'm using this

SELECT a.SecurityName,
       Sum( d.NetAmount) - Sum(e.NetAmount)As 'Opening Amount',
       Sum( d.Quantity) - Sum(e.Quantity) As 'Opening Number',
       Sum( d.NetAmount) / Sum( d.Quantity)As 'Opening Rate', 
       Sum( s.Quantity) As 'Number', 
       Sum( s.NetAmount) / Sum( s.Quantity) As 'Rate', 
       Sum( s.NetAmount) As 'Amount',
       Sum( p.Quantity) As 'Number',
       Sum( p.NetAmount) / Sum( p.Quantity) As 'Rate',
       Sum(  p.NetAmount) AS 'Amount', 
       IsNull(Sum( d.Quantity), 0) + (Sum( p.Quantity)) - IsNull((Sum( s.Quantity)), 0) As 'Closing Number',
       IsNull(Sum( d.NetAmount),0)+(Sum( p.NetAmount)) -IsNull((Sum( s.NetAmount)),0) As 'Closing Amount',
       IsNull(Sum( d.Rate),0)+(Sum( p.Rate))-IsNull((Sum( s.Rate)),0) As 'Closing Rate'
  FROM Sale s 
left Join SecurityAccount a ON s.SecurityAccountId = a.SecurityAccountId 
Right JOIN Purchase p ON a.SecurityAccountId = p.SecurityAccountId 
Left JOin Purchase d On a.SecurityAccountId=d.SecurityAccountId
                    And d.Date < @PeriodStart
Left Join Sale e On a.SecurityAccountId=e.SecurityAccountId
                And e.Date < @PeriodStart
Group by a.SecurityName

End"

so i'm getting values 3 times greater than expected

can anyone tell me what should i do or right query???

thanks in advance

Answer 1

You are joining tables 4 times by the same field SecurityAccountId. Each join will result multiplying of result rows. The only way I see is to create 4 subqueries with grouping and then using those results in main query. This should work, if I have no mistakes :)

SELECT a.SecurityName,
       (d.SumNetAmount - e.SumNetAmount) AS 'Opening Amount',
       (d.SumQuantity - e.SumQuantity) AS 'Opening Number',
       (d.SumNetAmount) / d.SumQuantity) AS 'Opening Rate', 
       s.SumQuantity AS 'Number', 
       (s.SumNetAmount / s.SumQuantity) AS 'Rate', 
       s.SumNetAmount AS 'Amount',
       p.SumQuantity AS 'Number',
       (p.SumNetAmount / p.SumQuantity) AS 'Rate',
       p.SumNetAmount AS 'Amount', 
       (ISNULL(d.SumQuantity, 0) + p.SumQuantity - ISNULL(s.SumQuantity, 0)) AS 'Closing Number',
       (ISNULL(d.SumNetAmount,0) + p.SumNetAmount - ISNULL(s.SumNetAmount,0)) AS 'Closing Amount',
       (ISNULL(d.SumRate,0) + p.SumRate - ISNULL(s.SumRate,0)) As 'Closing Rate'
FROM SecurityAccount a
LEFT JOIN (SELECT SecurityAccountId, 
       SUM(Quantity) AS 'SumQuantity', 
       SUM(NetAmount) AS 'SumNetAmount',
       SUM(Rate) AS 'SumRate'
FROM Sale) AS s ON a.SecurityAccountId = s.SecurityAccountId
LEFT JOIN (SELECT SecurityAccountId, 
       SUM(Quantity) AS 'SumQuantity', 
       SUM(NetAmount) AS 'SumNetAmount',
       SUM(Rate) AS 'SumRate'
FROM Sale WHERE Date < @PeriodStart) AS e ON a.SecurityAccountId = e.SecurityAccountId
LEFT JOIN (SELECT SecurityAccountId, 
       SUM(Quantity) AS 'SumQuantity', 
       SUM(NetAmount) AS 'SumNetAmount',
       SUM(Rate) AS 'SumRate'
FROM Purchase) AS p ON a.SecurityAccountId = p.SecurityAccountId
LEFT JOIN (SELECT SecurityAccountId, 
       SUM(Quantity) AS 'SumQuantity', 
       SUM(NetAmount) AS 'SumNetAmount',
       SUM(Rate) AS 'SumRate'
FROM Purchase WHERE Date < @PeriodStart) AS d ON a.SecurityAccountId = d.SecurityAccountId

Answer 2

really i would say you are genius and good man.. thanx for spending lots of time in my query thank you so much Pavel Morshenyuk you solved my problem

Regards Amanullah khan [email protected]