CODEWITHSUNDEEP
sqlsql-servert-sqlinner-joincase-statement
i.n.n.m
i.n.n.m

Reputation: 3046

Set a variable select matching rows using an inner join with case statement t-sql

I am trying to get matching rows from two tables using an inner join and if they all match then set to 0 else set to 1. Finally, I want to set that 1 or 0 into a variable @duplicate_counts.

Sample Data ClaimsTable, (Both, ClaimsTable and #tempTable have identical columns)

   Time             Terminal_ID   Count
------------------------------------
2017-10-19 06:03:00     1         5
2017-10-19 06:04:00     1         2
2017-10-19 06:05:00     1         2
2017-10-19 06:06:00     1         2
2017-10-19 06:03:00     9         2
2017-10-19 06:04:00     9         3
2017-10-19 06:05:00     9         2
2017-10-19 06:06:00     9         3
2017-10-19 06:06:00     9         3

#tempTable,

 Time             Terminal_ID   Count
 ------------------------------------
 2017-10-19 06:03:00        1         5
 2017-10-19 06:04:00        1         2
 2017-10-19 06:05:00        1         2
 2017-10-19 06:06:00        1         2
 2017-10-19 06:03:00        9         2
 2017-10-19 06:04:00        9         3
 2017-10-19 06:05:00        9         2
 2017-10-19 06:06:00        9         3

Now, I want to check the matching rows of both tables using innerjoin. If they have matching rows, I want to set the variable @duplicate_counts to 0 and if they don't I want to set @duplicate_counts to 1. In this case, ClaimsTables's last record is a duplicate.

All I am doing is a check to see if there is any to make sure I don't have any duplicates.

I tried CASE Statement like this,

declare @duplicate_counts int
    set @duplicate_counts =(select 
    CASE WHEN
    (
        select count(*) 
        from ClaimsTable
        inner join #temptable
            on #temptable.[time] = ClaimsTable.[time] 
            and #temptable.terminal_id = ClaimsTable. terminal_id
    )
    THEN 0
    ELSE 1
END
select @duplicate_counts

This, statement gave me an error:

An expression of non-boolean type specified in a context where a condition is expected, near 'then'.

After this, I will do an if statement like this, 

if @duplicate_counts = 1 
            begin
            --insert duplicates into bin table
            insert into BinTable
            ...

This way, the last record of the ClaimsTable will be inserted to BinTable.

How can I set to 0 if they match else 1 in case statement?

Upvotes: 0

Views: 1619

Answers (2)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

declare @duplicate_counts int 
IF
    (
        select count(*) 
        from ClaimsTable
        inner join #temptable
            on #temptable.[time] = ClaimsTable.[time] 
            and #temptable.terminal_id = ClaimsTable. terminal_id
    ) = (select count(*) from #temptable) 
    set @duplicate_counts = 0
 ELSE 
    set @duplicate_counts = 1

select @duplicate_counts

Upvotes: 1

Gez Jones
Gez Jones

Reputation: 214

Not entirely sure what you are going for here. I would simply get the count and then insert the duplicates separately, but this would do it the way you are looking for:

declare @duplicate_counts int
set @duplicate_counts = (
SELECT SELECT COUNT(0) - SUM( CASE WHEN #temptable.terminal_id IS NULL THEN 1 ELSE 0 END)
FROM ClaimsTable LEFT OUTER JOIN #temptable 
ON #temptable.[time] = ClaimsTable.[time] 
AND ClaimsTable.terminal_id = #temptable.terminal_id
)
select @duplicate_counts

Upvotes: 0

Related Questions