Mario Mario - 7 months ago 8
SQL Question

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

Answer

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.

Comments