CODEWITHSUNDEEP
sqlsql-servert-sql
Mars
Mars

Reputation: 534

Fetch data if exist in distinct of another column

I am trying to generate the query to fetch the data on the basis of company which have tax paid record in each year of : 

Select * 
From (
  Select 
       CompanyName
     , TaxPaidOn
     , DATEPART(Year, TaxPaidOn) as 'TaxPaidYear'
     , OtherInfo
  From
  TestData
) As BaseTable
Where CompanyName IN (Distinct TaxPaidYear)

I know the above query is wrong. Will you please rectify it?

Sample data are as below:

Script :

CREATE TABLE TestData(
    CompanyName nvarchar(50) NOT NULL,
    TaxPaidOn date NOT NULL,
    OtherInfo nvarchar(max) NULL
)
GO
INSERT TestData (CompanyName, TaxPaidon, OtherInfo) 
VALUES 
 ('ABC', '2009-01-01', 'Information1')
,('ABD', '2009-01-01', 'Information2')
,('ABE', '2009-01-01', 'Information3')
,('ABF', '2009-01-01', 'Information4')
,('ABC', '2010-01-01', 'Information5')
,('ABE', '2010-01-01', 'Information6')
,('ABF', '2010-01-01', 'Information7')
,('ABC', '2011-01-01', 'Information8')
,('ABD', '2011-01-01', 'Information9')
,('ABE', '2011-01-01', 'Information10')

TestData Table :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABD             2009-01-01   Information2
ABE             2009-01-01   Information3
ABF             2009-01-01   Information4
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABF             2010-01-01   Information7
ABC             2011-01-01   Information8
ABD             2011-01-01   Information9
ABE             2011-01-01   Information10

Expected Result :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABE             2009-01-01   Information3
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABC             2011-01-01   Information8
ABE             2011-01-01   Information10

Upvotes: 1

Views: 44

Answers (1)

Gabriel Durac
Gabriel Durac

Reputation: 2770

SELECT          t.* 
FROM            TestData t 
WHERE 
EXISTS ( 
  SELECT        1 
  FROM          TestData t2
  WHERE         t2.companyName = t.CompanyName
  GROUP BY      t2.companyName 
  HAVING        COUNT(DISTINCT YEAR(TaxPaidOn)) = (
    SELECT      COUNT(DISTINCT YEAR(TaxPaidOn)) 
    FROM        TestData
  )

Or this should be more efficient:

SELECT          t.* 
FROM            TestData t 
WHERE           companyName 
IN ( 
  SELECT        CompanyName 
  FROM          TestData  t2
  GROUP BY      t2.companyName 
  HAVING        COUNT(DISTINCT YEAR(TaxPaidOn)) = (
    SELECT      COUNT(DISTINCT YEAR(TaxPaidOn)) 
    FROM        TestData
  )
)

Edit: Improved based on @Eric-Brandt's suggestion

Upvotes: 3

Related Questions