CODEWITHSUNDEEP
sqlsql-server
dave haughton
dave haughton

Reputation: 45

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

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 trying 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 don't 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

Upvotes: 4

Views: 4169

Answers (3)

SidC
SidC

Reputation: 3203

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

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

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

Upvotes: 0

dcp
dcp

Reputation: 55449

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

Upvotes: 1

Related Questions