CODEWITHSUNDEEP
sql-servert-sqlcomparesql-server-2014except
Conrad Addo
Conrad Addo

Reputation: 434

Insert 1 or 0 where EXCEPT returns a value when comparing tables

Using SQL Server 2014, how can I return a table of 1's or 0's and then count the 1's where there is a match in like tables for multiple field names, grouping by [Loan Identifier]. I currently have a table called dbo.Tape which is the original table and then dbo.[Tape Capture] which users are updating. The [Loan Identifier] is identical in both tables.

Thanks if you can help. 

SELECT       
   [Loan Identifier], [Completion date], [Tenure], [Underwriter name] 
FROM 
   dbo.Tape

EXCEPT

SELECT      
    [Loan Identifier], [Completion date], [Tenure], [Underwriter name]  
FROM 
    dbo.[Tape Capture]

If the [Completion date] in dbo.Tape matched the [Completion date] in dbo.[Tape Capture], then I would like to return 1, else zero. Then I would like to sum all of these values in each column. For example,

dbo.[Tape Capture]:

[Loan Identifier], [Completion date], [Tenure], [Underwriter name] 
1                    01/01/2016       Freehold     James Mac
2                    01/01/2016       Leasehold    James Mac
3                    02/01/2016       Freehold     James Mac
4                    01/01/2016       Leasehold    James Mac
5                    03/01/2016       Freehold     James Mac

dbo.Tape:

[Loan Identifier], [Completion date], [Tenure], [Underwriter name] 
1                    01/01/2016       Freehold     James Mac
2                    01/01/2016       Freehold     James Mac
3                    01/01/2016       Freehold     James Mac
4                    01/01/2016       Freehold     James Mac
5                    01/01/2016       Freehold     James Mac

Then I would like the results table to hold:

[Loan Identifier], [Completion date], [Tenure], [Underwriter name] 
1                    1                  1          1
2                    1                  0          1
3                    0                  1          1
4                    1                  0          1
5                    0                  1          1

From here, I would like to sum up the values in each column.

Upvotes: 0

Views: 49

Answers (1)

Deep Kalra
Deep Kalra

Reputation: 1428

SELECT T.[Loan Identifier], 
       CASE 
         WHEN TC.[Completion date] = T.[Completion date] THEN 1 
         ELSE 0 
       END AS [Completion date], 
       CASE 
         WHEN TC.[Tenure] = T.[Tenure] THEN 1 
         ELSE 0 
       END AS [Tenure], 
       CASE 
         WHEN TC.[Underwriter name] = T.[Underwriter name] THEN 1 
         ELSE 0 
       END AS [Underwriter name] 
FROM   [dbo].[Tape] AS T 
       LEFT JOIN [dbo].[Tape Capture] AS TC 
              ON T.[Loan Identifier] = TC.[Loan Identifier]

Upvotes: 2

Related Questions