>>select sum(amount) as igabudget, fundtype, phase from RTA_IGABudgets where TipID=@TipID group by Fundtype, phase >> >>select sum(amount) as expended, fundtype, phase from TIP_Ledger2 where TipID=@TipID and fisyear<@Tipyear group by FundType, phase >> >>select sum(amount)*1000 as programmed, fundtype, phase from TIP_ApprovedFunding where TipID=@TipID and year<=@tipyear group by fundtype, phase >> >>select SUM(amount)*1000 as futureprogrammed, fundtype, phase from TIP_ApprovedFunding where TipID=@TipID and YEAR>@Tipyear group by FundType, PHASE >>>>
>SELECT fundtype, > phase, > SUM(CASE WHEN Type = 1 THEN Amount END) AS igabudget, > SUM(CASE WHEN Type = 2 THEN Amount END) AS expended, > SUM(CASE WHEN Type = 3 THEN Amount END) AS programmed, > SUM(CASE WHEN Type = 4 THEN Amount END) AS futureprogrammed >FROM (select amount as Amount, > fundtype, > phase, > 1 AS Type > from RTA_IGABudgets > where TipID=@TipID > UNION ALL > select amount as Amount, > fundtype, > phase, > 2 AS Type > from TIP_Ledger2 > where TipID=@TipID > and fisyear<@Tipyear > UNION ALL > select amount*1000 as Amount, > fundtype, > phase, > 3 AS Type > from TIP_ApprovedFunding > where TipID=@TipID > and year<=@tipyear > UNION ALL > select amount*1000 as Amount, > fundtype, > phase, > 4 AS Type > from TIP_ApprovedFunding > where TipID=@TipID > and YEAR>@Tipyear > ) Test >group by FundType, PHASE >>