1

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
2
  • I have some considerations about my answer: 1st - I'm relying on the fact that no company will pay taxes more than once a year. If it happens, the result will be wrong. 2nd - I assumed, based on your expected result, that you wanted to list only the companies that have paid taxes each year instead of distincted companies as you described on the question. Are you ok with that? – Ronaldo Apr 28 '20 at 2:40
  • Your answer is good on current requirement, I don't have repetitive row on same year but, In real world it can be multiple on country tax rule and fiscal year period. – Mars Apr 28 '20 at 4:30
2

I could achieve the result you expected with this query:

;WITH CTE (CompanyName, Total)
AS
(
    SELECT companyName, COUNT(1)
    FROM TestData
    GROUP BY CompanyName
)
SELECT TD.CompanyName, TD.TaxPaidOn, TD.OtherInfo
FROM CTE
    INNER JOIN TestData AS TD 
    ON CTE.CompanyName = TD.CompanyName
WHERE Total = (SELECT MAX(Total) FROM CTE)
ORDER BY TD.TaxPaidOn;

It doesn't have a DISTINCT though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.