I have a query that returns the name of a Project, the last invoice date, the number of days past due and the project manager for projects that have not been invoiced in the last 45 days. This is it and it works well
SELECT ClientID, Cltname, Cltnum, CltEng, Status, LastInvoiceDate, CurrentDate, NumberOfDays, CBMnum
FROM (SELECT a.ClientID, c.Cltname, c.Cltnum, c.CltEng, c.Engstatus AS Status, MAX(a.InvoiceDate) AS LastInvoiceDate, GETDATE() AS CurrentDate, DATEDIFF(day,
MAX(a.InvoiceDate), GETDATE()) AS NumberOfDays, c.CBMnum
FROM ARC_Invoices AS a LEFT OUTER JOIN
Clients AS c ON a.ClientID = c.ID
WHERE (c.Engstatus = 0)
GROUP BY a.ClientID, c.Engstatus, c.Cltname, c.Cltnum, c.CltEng, c.CBMnum) AS a_1
WHERE (NumberOfDays > 45)
ORDER BY ClientID, LastInvoiceDate DESC
Now we have a table with comments and I'd like to include the comments in the above query but there could be more than one comment for each ClientID. Is there an easy way to do this? The comments would be tied to the ClientID so I thought I could do another SELECT statement within my SELECT statement before the FROM clause but I'm all messed up by everything I've found online and all.
If anyone can help it would be greatly appreciated!!!!