Search code examples
sqloracle-database

Writing SQL Query With Same Client ID , same Tax_year, same Tax_effective_date But with Different Tax_id & Payroll_ID in ORACLE SQL

I have a table like below data: enter image description here

Now One query need to be written where logic will be like: Same client Id(duplicate client_id), same Tax_year, same Tax_effective_date but with different tax_code and Payroll_id.Other column's data should be displayed as it is.

Expected Output:

enter image description here

I have tried to write the query using CTE table, but not able to get proper result. Could anyone please help me on this to write the query.

Solution

  • Same client Id(duplicate client_id), same Tax_year, same Tax_effective_date but with different tax_code and Payroll_id.

    You can use multiple columns filtering in Where clause using IN(... subquery ...)

    --      S Q L : 
Select   t.*
From     tbl t
Where  (CLIENT_ID, TAX_YEAR, TAX_EFFECTIVE_DATE) 
        IN( Select   CLIENT_ID, TAX_YEAR, TAX_EFFECTIVE_DATE
            From     tbl 
            Group By CLIENT_ID, TAX_YEAR, TAX_EFFECTIVE_DATE 
            Having Count(*) > 1 And                   -- Same client Id(duplicate client_id), same Tax_year, same Tax_effective_date
                   Count(DISTINCT TAX_CODE) > 1 And   -- different tax_code
                   Count(DISTINCT PAYROLL_ID) > 1)    -- different payroll_id
Order By t.CLIENT_ID, t.TAX_CODE

    /*      R e s u l t :
CLIENT_ID   TAX_CODE    TAX_YEAR    TAX_EFFECTIVE_DATE  PAYROLL_ID  GROSS_PAID  TAX_PAID
----------  ----------  ----------  ------------------  ----------  ----------  --------
123         BR          2018        12-NOV-17           780PR               56         0
123         MT          2018        12-NOV-17           673Y             43.94     12.23
789         789Y        2017        12-NOV-17           4567Z            43.94     12.23
789         GH          2017        12-NOV-17           4567R            43.94     12.23

    See the fiddle here.