SQL Query for union all the months

Question

Hi all,

 

I need to get data to all months for the selected year like this picture

 

 

this is what i hv done. please send me an answer

Declare @Temp Table
(
CompanyID int,
ID nvarchar(250) NOT NULL,
Name nvarchar(250) ,
ParentAccID nvarchar(250),
ParentAccName nvarchar(250),
ParentDisplayID nvarchar(250),
DisplayID nvarchar (250),
Actual decimal(18,2),
Budget decimal(18,2),
Classification nvarchar(250),
AccLevel int,
AccOrder int
);
INSERT INTO @Temp
select
4011111111115156122,NEWID(),'GROSS PROFIT','','GP','7-0000','7-0000',0,0,'GrossProfit',1,3
union
select
4011111111115156122,NEWID(),'OPERATING PROFIT',null,'OP','10-000','10-000',0,0,'OperatingProfit',1,5
union
select
4011111111115156122,NEWID(),'NET PROFIT / LOSS','','P/L','11-000','11-000',0,0,'NetProfit/Loss',1,8
INSERT INTO @Temp
select
[CompanyID],
[UID],
[Name],
[ParentAccountId],
[ParentAccountName],
[ParentDisplayID],
[DisplayID],
0,
0,
[Classification],
[AccountLevel],
(case when Classification = 'Income' then 1 when Classification='CostOFsales' then 2 when Classification='Expense' then 4 when Classification='OtherIncome' then 6 when Classification='OtherExpense' then 7 else 0 end) as AccOrder
from Account111
where CompanyID=4011111111115156122
AND Classification in('Income','Expense','CostOfSales','OtherIncome','OtherExpense')
-- Actual for Month
update Tupdate set
Actual = isnull((select sum(Activity) from AccountReg where CompanyID = 4011111111115156122 and Year = 2012 and Month=1 and convert(varchar(250),UID) = Tupdate.ID) ,0)
from @Temp as Tupdate
---- Budget for Month
update Tupdate set
Budget = isnull((select sum(Amount) from AccountBud where CompanyID = 4011111111115156122 and Year = 2012 and month=1 and convert(varchar(250),AccountUID) = Tupdate.ID) ,0)
from @Temp as Tupdate
update Tupdate set
Actual = ((select Actual from @Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - (select Actual from @Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122)),
budget = ((select Budget from @Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - (select Budget from @Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122))
from
@Temp as Tupdate where DisplayID = '7-0000' and CompanyID = 4011111111115156122
update Tupdate set
Actual = ((select Actual from @Temp where DisplayID = '7-0000' AND CompanyID = 4011111111115156122 ) - (select Actual from @Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122)),
budget = ((select Budget from @Temp where DisplayID = '7-0000' AND CompanyID = 4011111111115156122 ) - (select Budget from @Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122))
from
@Temp as Tupdate where DisplayID='10-000' and CompanyID = 4011111111115156122
update Tupdate set
Actual = ((select Actual from @Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - (select Actual from @Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122) - (select Actual from @Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122) + (select Actual from @Temp where DisplayID = '8-0000' and CompanyID = 4011111111115156122)- (select Actual from @Temp where DisplayID = '9-0000' and CompanyID = 4011111111115156122) ),
budget = ((select Budget from @Temp where DisplayID = '4-0000' AND CompanyID = 4011111111115156122 ) - (select Budget from @Temp where DisplayID = '5-0000' and CompanyID = 4011111111115156122) - (select Budget from @Temp where DisplayID = '6-0000' and CompanyID = 4011111111115156122) + (select Budget from @Temp where DisplayID = '8-0000' and CompanyID = 4011111111115156122)- (select Budget from @Temp where DisplayID = '9-0000' and CompanyID = 4011111111115156122))
from
@Temp as Tupdate where DisplayID='11-000' and CompanyID = 4011111111115156122
select * from @Temp order by AccOrder, AccLevel,ID

thank you