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

Rami Reddy 1 Reputation point
2020-04-20T07:01:47.903+00:00

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  

7498-original-data.png

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:
7602-exceptedresult.png

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?

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,931 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Leon Laude 85,691 Reputation points
    2020-04-20T07:03:34.977+00:00

    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

    0 comments No comments

  2. Vaibhav Chaudhari 38,666 Reputation points
    2020-04-20T12:11:59.713+00:00
    0 comments No comments