Further to Solution 1 ... where ArunRajendra is exactly right in describing the approach you should take to finding the problem...
Most of your sub-queries are returning
sum
of something ... except this one
(Select distinct(volumn) from Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title) As Volumn
... which I suspect is the cause of your problem.
Another way you can make life easier for yourself is to use whitespace ... spaces, tabs, newlines ... this is your unaltered code reformatted with some whitespace to make it more readable ...
ALTER procedure [dbo].[track_order1]
@grnid int,
@insid int,
@piid int
as
begin
Select distinct pii.inst_id, pd.PIid, pd.title,pd.publisher,pd.format ,pd.subscription_period_from as Sub_from, pd.subscription_period_to as sub_to,pd.Qty_in_year AS Frequency ,
(
Select distinct(volumn)
from Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title
) As Volumn,
Coalesce(
(
Select Sum(cast(Dispatch_Qty as int)) from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid
and title= pd.title and order_Status=2
) ,0)as Rec_qtys,
pd.Qty_in_year - Coalesce(
(
Select Sum(cast(Dispatch_Qty as int))
from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=2
) ,0) as rem,
Coalesce(
(
Select Sum(cast(Dispatch_Qty as int)) from
dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and title= pd.title and order_Status=1
),0) as New_dispatch,
case pd.Qty_in_year - Coalesce(
(
Select Sum(cast(Dispatch_Qty as int))
from dbo.Stock_Dispatch where inst_id=@insid and PIid=@piid and
title= pd.title and order_Status=2
) ,0)
when 0 then 'Complete'
else 'Not Completed' end as Order_stat
from
dbo.Performa_details pd,
Client_Goods_details_master po,
Stock_Dispatch sd
right outer join Performa_details pdio on pdio.PIid=sd.PIid,
Performa_invoice pii
where po.PIid=pd.PIid and pd.PIid=pii.PI_id and Grnid=@grnid and pii.inst_id=@insid
end
Once I had done that it was very easy to spot the difference in the sub-queries.
I also notice that you have a mix of methods of joining tables. My advice would be to be consistent - I personally prefer to use
ON
clauses as it makes it easier to see what the actual filter being used is and also to spot any errors. I had an attempt at your from clause
from
dbo.Performa_details pd
inner join Client_Goods_details_master po on po.PIid=pd.PIid
Stock_Dispatch sd,
right outer join Performa_details pdio on pdio.PIid=sd.PIid,
inner join Performa_invoice pii on pd.PIid=pii.PI_id
where Grnid=@grnid and pii.inst_id=@insid
and couldn't see how
Stock_Dispatch
was linked to the other tables (other than Performa_details)