i need to do a view in sql that returns the latest invoice date for each company

Question

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

Answer 1

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

Answer 2

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 3

I would use a GROUP BY and a MAX. Quick & 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