CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Vijay
Vijay

Reputation: 49

Remove records from Select when both conditions are met in Snowflake SQL

I am trying to get remove records from a CTE when this criterion is met -

Remove timesheets against Position ID 0000087544  and pay code is SUPA

select * from timesheets where ( (external_payroll_code not in ('SUPA')) and (position_id  not in ('0000087544')) )

is removing all records position_id = '0000087544' when i want only those position ids where external_payroll_code = 'SUPA' to be removed.

is there a way i can specify an conditional exclusion in SELECT as a subquery in Snowflake SQL or writing a JOIN clause the only way out.

Any hints will be appreciated. Thanks!

Input

Employee ID POSITION ID EXTERNAL PAYCODE
1A 0000087544 SUPA
1B 00000888888 SUPA

Output

Employee ID POSITION ID EXTERNAL PAYCODE
1B 00000888888 SUPA

Upvotes: 0

Views: 673

Answers (1)

Nat Taylor
Nat Taylor

Reputation: 1108

You just need to change the AND/OR logic around a bit.

with timesheets as (select $1 employee_id, $2 position_id, $3 external_payroll_code from values ('1A',  '0000087544',   'SUPA'), ('1B', '00000888888',  'SUPA'))

select * from timesheets where not (external_payroll_code in ('SUPA') and position_id  in ('0000087544'))

EMPLOYEE_ID POSITION_ID EXTERNAL_PAYROLL_CODE
1B  00000888888 SUPA

Upvotes: 1

Related Questions