Hi,
I want to take the sum of a table1 and display it in table two:
I am using two tables named Table1 & Table2
I am using left outer join for joining both the tables in that table I want to take the Sum of the Amount from a table the stored procedure I have return like this
select Distinct 'BL' as BillID, '' Currency, SUM(P.BOQPriceINR)as PriceINR
,SUM(P.BOQCOSTINR) as COSTINR ,S.ProjectMilestoneId from Table1 S with(nolock)
left outer join Table2 P with(nolock) on P.Code=S.Code and P.Id=s.Id and p.tRevNo=S.RevNo
where S.Code=001 and S.Id=1113120007 and S.RevNo=0 group by S.ProjectMilestoneId
If I execute this stored procedure It is duplicating the row because the table2 contains the field called currncycode the currency code will be in INR or USD If I take sum both the currency it is duplicating. I want to display the sum in single line. How to do this one Please help me to solve this issue.
EG:
BillID Currency PriceINR COSTINR ProjectMilestoneId
BL1 5000 4000 1 from Table1
BL1 2000 3000 1 from Table1
BL1.1 INR 5000 4000 from table2
BL1.1 USD 2000 3000 from Table2
I want the answer like this
BillID Currency PriceINR COSTINR ProjectMilestoneId
BL1 7000 7000 1 from Table1
BL1.1 INR 5000 4000 from table2
BL1.1 USD 2000 3000 from Table2
Thanks in Advance