Skip to content
Advertisement

field subtraction sql server

If I would like to subtract the fields from each other,

i.e. in A there are 11 fields described as ‘Faktura zakupu’ and in B there are 5 fields described as ‘Faktura zakupu’. I would like to get a return of records in the form of 6 items ‘Faktura zakupu’ (11-5 = 6)

I tried the EXCEPT operation, but it does not return the desired results

what operation do i need to perform?

enter image description here

Advertisement

Answer

You can add row number to each row in both tables. Then SQL Server can determine that the first (Faktura zakupu, Original) in table A is a duplicate of the first (Faktura zakupu, Original) in table B and remove it during EXCEPT operation:

SELECT Name, StatusReq, ROW_NUMBER() OVER (PARTITION BY Name, StatusReq ORDER BY (SELECT NULL))
FROM a

EXCEPT

SELECT Name, StatusReq, ROW_NUMBER() OVER (PARTITION BY Name, StatusReq ORDER BY (SELECT NULL))
FROM b

It’ll return 6 rows from table A… numbered 6 through 11.

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement