sqlsql-serversql-except

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

Solution

  • 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.