SQL Query for union all the months

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kapil Singh Kumawat8y ago
The output that you want can be achieved using Pivot in sql server. Please check this link for information and example about pivot - https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017
Nitin Sontakke8y ago
I think you have made it complex unnecessarily. There are simple ways to achieve the same result, I think.

If you give some simple sample data in script format (so that I don't have to type it all again) and an expected output, may be, I can provide you the query using pivot.

OR if you just google word pivot in sql server, you will the examples online.
Kasuni Abegunawardana8y ago
As u can see here i am using one temporary table and getting records into the selected month and year.
Here using Update one i am giving the parameters to selected year and month.
That's completed but now i want to get the data for all the months. If I have selected 2015 with "All" as month it should give me all the month Jan - Dec.
Can u say how to update that query
Nitin Sontakke8y ago
The output that you have pasted can be obtained by using pivot SQL Server keyword. Have you already looked into that?

The query you posted is too complex to look into. Provide some real simple sample data and so as to work on it.