CODEWITHSUNDEEP
mysqlsql
Mario
Mario

Reputation: 3

SQL Query - Filter Largest Date from a group of clients

I currently have two tables; Customers and Tax Returns

Customers:

Sysref(PK) FullName
1          ABC Ltd
2          XYZ Ltd

Tax Returns:

CustomerSysref(FK) TaxReturnYear
1                  2016
1                  2015

As part of the query I am joining Tax Returns to Customer Table. 

FROM CUSTOMERS CUS
LEFT JOIN TaxReturns CITR ON CITR.ClientSysRef = CUS.SYSREF

I need to query the result of the largest date. Taking the example above I need the query to output the TaxReturnYear for 2016. Can someone please guide me?

If I want to add a where clause which will not display the row when the SubmissionDate is equal to 1900? example;

Tax Returns

CustomerSysref(FK) TaxReturnYear SubmissionDate
1                  2016          1900
1                  2015          2016 

WHERE CITR.SubmissionDate != '1900'

The result would be;

2015 even though 2016 is larger, however the SubmissionDate is 1900.

End result needs to display;

Sysref & FullName from table customers

TaxReturnYear and SubmissionDate from table Tax Returns

Upvotes: 0

Views: 60

Answers (1)

Serg
Serg

Reputation: 22811

If i got it right 

...
FROM CUSTOMERS CUS
LEFT JOIN 
  (SELECT CustomerSysref, max(TaxReturnYear) as TaxReturnYear 
   FROM TaxReturns
   WHERE SubmissionDate != '1900'
   GROUP BY CustomerSysref ) AS CITR
ON CITR.ClientSysRef = CUS.SYSREF
-- added to get submissionDate
LEFT JOIN TaxReturns tr2 
ON CITR.CustomerSysref = tr2.CustomerSysref AND CITR.TaxReturnYear = tr2.TaxReturnYear

Corrected according to the question edit.

Upvotes: 2

Related Questions