Hi All, I have the following tableCREATE TABLE [dbo].[TblReport]( [Name] [varchar](50) NULL, [PlanAmt] [money] NULL, [app] [varchar](50) NULL, [Number] [varchar](50) NULL, [Request] [varchar](50) NULL, [Amount] [money] NULL, [Year] [varchar](50) NULL) ON [PRIMARY]GO
I have the following data inserted in the table:INSERT INTO [dbo].[TblReport] ([Name] ,[PlanAmt] ,[app] ,[Number] ,[Request] ,[Amount] ,[Year]) SELECT 'A1',6000,'TECH',1234,'4532',6000,'2015' UNION ALL SELECT 'R1',400000,'TECH',3456,'6543',2000 ,'2015' UNION ALLI have SELECT 'R1' , 400000 , 'TECH', 3456, '6543' , 1000 , '2015' UNION ALL SELECT 'R1', 400000, 'TECH' , 3456 , '6543' , 2000, '2015' UNION ALL SELECT 'R1', 400000 , 'TECH' ,3456 , '6543' , 2000 , '2015' UNION ALL SELECT 'R1', 400000, 'TECH', 3456, 6543 , 2000 , '2015' UNION ALL SELECT 'R1' , 500000 , 'TECH' , 3456, '6547' , 20000, '2016' UNION ALL SELECT ' R1' , 400000 , 'TECH', 2222 , '6548' , 1000 , '2015' UNION ALL SELECT 'R1' , 400000, 'TECH' , 2222 , '6548', 1000 , '2015' UNION ALL SELECT 'R1' , 500000 , 'Admin', 3333 , ' 6543', 1000, '2016' UNION ALL SELECT 'B1' , 170000 , 'Admin', 8976 , ' 3451' , 2000 ,'2015' UNION ALL SELECT 'B1' , 170000 , 'Admin', 8976, '3451' , 1000, '2015' UNION ALL SELECT 'B1', 170000 , 'Admin', 8976 , '3451' , 1000 , '2015' UNION ALL SELECT 'B1' , 170000 , 'TECH', 8976, '3456' , 7000, '2015'
The following table looks like this: Name PlanAmt app Number Request Amount Year A1 60000 Tech 1234 4532 60000 2015 R1 400000 TECH 3456 6543 2000 2015 R1 400000 TECH 3456 6543 1000 2015 R1 400000 TECH 3456 6543 2000 2015 R1 400000 TECH 3456 6543 2000 2015 R1 400000 TECH 3456 6543 2000 2015 R1 500000 TECH 3456 6547 20000 2016 R1 400000 TECH 2222 6548 1000 2015 R1 400000 TECH 2222 6548 1000 2015 R1 500000 Admin 3333 6543 1000 2016 B1 170000 Admin 8976 3451 2000 2015 B1 170000 Admin 8976 3451 1000 2015 B1 170000 Admin 8976 3451 1000 2015 B1 170000 TECH 8976 3456 7000 2015
I need this below: Name Planamount app year Number Request amount Remain A1 60000 TECH 2015 1234 4532 60000 0 R1 400000 TECH 2015 3456 6543 9000 391000 Admin 2222 6548 1000 390000 R1 500000 TECH 2016 3456 6547 20000 480000 Admin 3333 6543 1000 479000 B1 170000 TECH 2015 8976 3456 7000 163000 B1 Admin 8976 3451 4000 159000Grand Total 1130000 101000 2457000
Basically, I want the report grouped by name and then by app and then year. I want to sum the amount when the name, app and year are same and put it in amount columns and in the remain column, I want (PlanAmount - amount) as long as name, app and year are same. I have four categories in app 'TECH', 'ST', 'SA', 'Admin', 'N'F. I want it to appear in the same order first Tech and then ST and then SA and last one is NF. I only have TECH and admin in the above tabel, but there will be more. If the number is same and name,app and year is same then we can add the amount of that number and put it together in amount column and then finally subtract the planned amount from the amount. I also want the Grand total of Plan amount, amount and remain. any help will be greatly appreciated.Please let me know if I need to clarify anything. I really need help with this query. I am using sql server 2012
sql query help
Posted - 2015-04-10 : 11:17:02
