i need to do a view in sql that returns the latest invoice date for each company
- by dave haughton
hi, i have a company table that is dbo.companies and has companyId as a column. I also have an invoice table that is dbo.invoices with invoicecompanyId column (which is the same as the companyId on the other table) and it also has a column called invoicedate.
What i am mtrying to achieve is a view of each companyid with the corresponding latest invoice date for all the companies i have.
i have done the following but i dont know how to filter for the latest invoice, it returns all invoices from all companies and i need latest invoice for all companies
SELECT TOP (100) PERCENT
'A' +
SUBSTRING('000000', 1, 6 - LEN(CAST(dbo.companies.companyId AS varchar(10)))) +
CAST(dbo.companies.companyId AS varchar(10)) AS Client_ID,
dbo.invoices.invoiceDate AS S_Inv_Date
FROM dbo.invoices
INNER JOIN dbo.companies
ON dbo.invoices.invoiceCompanyId = dbo.companies.companyId
ORDER BY Client_ID
can you help please
ta
