select a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period, >>convert(integer,SUM(l.amount)/1000) as expended >>from RTA_Elements a >>left join TIP_Projects b on a.ElementNum = convert(integer,substring(b.RtaID,5,2)) >>left join TIP_Ledger l on b.TipID = l.tipid and l.Fundtype = 'RTA' >>group by a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period >>order by elementnum>>
>>select a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period, >>convert(integer,SUM(l.amount)/1000) as expended, sum(p.amount) as programmed >>from RTA_Elements a >>left join TIP_Projects b on a.ElementNum = convert(integer,substring(b.RtaID,5,2)) >>left join TIP_Ledger l on b.TipID = l.tipid and l.Fundtype = 'RTA' >>left join TIP_ApprovedFunding p ON b.TipID = p.TipID and p.Fundtype = 'RTA' >>group by a.element, a.subelement, a.elementnum, a.Lead, a.TotalCost, a.OtherFunds, a.Budget, a.Period >>order by elementnumthe values for expended (which were correct before) and the values for programmed, come out wrong.