question

RamiReddy-1394 avatar image
0 Votes"
RamiReddy-1394 asked ·

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

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-supported
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LeonLaude avatar image
0 Votes"
LeonLaude answered ·

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


· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered ·
· Share
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.