SQL SERVER - based on another column value, we need to subtract the column value group by id.

Question

Below is the my table script:

declare  @t table  
(  
    id int IDENTITY(1,1),  
	CompanyID int,  
    OriginalValue int,  
	newvalue int  
)  
insert into @t (CompanyID, OriginalValue, newvalue)  
	VALUES (1,5, 10),  
		(1,3, 10),  
		(1,3, 10),  
		(2,10, 21),  
		(2,11, 21),  
		(3,5, 4),  
		(3,6, 4),  
		(4,5, 20),  
		(4,10, 20)		  
  
select * from @t  

Above is my table script and based on 'newvalue' column value, we need to subtract the 'originalvalue' column value group by companyid.

Below is the expected output:

CompanyID#1
id#1 --> 0 (10-5 = 5 is the remaining value, (newvalue - originalvalue)
id#2 --> 0 (5-3= 2 is the remaining value)
id#3 --> 1 (2-3 = 5 is the remaining value)

CompanyID#2
id#4 --> 0 (21-10 = 11 is the remaining value
id#5 --> 0 (11-11= 0 is the remaining value)

CompanyID#3
id#6 --> 1 (4-5= 0 is the remaining value)
id#7 --> 6 (0-6 = 0 is the remaining value)

CompanyID#4
id#8 --> 0 (20-5= 15 is the remaining value)
id#9 --> 0 (15-10 = 0 is the remaining value)

Could you please help on this?

Answer 1

Hi,

Q&A currently supports the products listed in right-hand pane (more to be added later on).

You can reach the experts in the dedicated SQL Server forum over here:
https://social.technet.microsoft.com/Forums/en-US/home?category=sqlserver

(please don't forget to mark helpful replies as answer)

Best regards,
Leon

Answer 2

FYI. This question has been posted here. Just providing the link for future users - https://social.msdn.microsoft.com/Forums/en-US/52a4d261-a92a-4ba4-84ea-2a67a616d0ef/based-on-another-column-value-we-need-to-subtract-the-column-value-group-by-id?forum=transactsql