I need to make a view in sql that returns the latest invoice date for each company

Question

I have a company table which is dbo.companies and has companyId as a column. I also have a table of accounts, which is dbo.invoices with an dbo.invoices column (which is the same as companyId in another table), as well as a column called invoicedate . What I am trying to achieve is the presentation of each companyId with the corresponding last invoice date for all the companies that I have.

I did the following, but I don’t know how to filter the last account, it returns all accounts from all companies, and I need the last account 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 

You can help?

that

Answer 1

 SELECT SUBSTRING('000000', 1, 6 - LEN(CAST(dbo.companies.companyId AS varchar(10)))) + CAST(dbo.companies.companyId AS varchar(10)) AS Client_ID , b.mxDate maxInvoiceDate FROM dbo.companies c , (SELECT dbo.invoices.invoiceCompanyId companyId , MAX(dbo.invoices.invoiceDate) mxDate FROM dbo.invoices GROUP BY dbo.invoices.invoiceCompanyId ) b WHERE c.companyId = b.companyId ORDER BY 1 

Answer 2

Can you order by S_Inv_Date descending to get the necessary results?

Answer 3

I would use GROUP BY and MAX. Quick and dirty sample:

 SELECT cy.companyId, cy.CompanyName, max(inv.invoiceDate) as LastInv FROM companies as cy, invoices as inv GROUP BY cy.companyId, cy.CompanyName WHERE cy.companyId = inv.companyId