--Company is made up of: company_id(PK), company_name, company_city
--Projects is made up of: project_id(PK), company_id(FK), project_name
--Notes is made up of: note_id(PK), project_id(FK), note_text, note_date, note_author

--I would like to have returned:
--company_id, company_name, company_city, [no of projects], [most recent note date], [author of that most recent note]
--
--Before I complicated it with note_author, I had the following command:
--SELECT company.company_id, company.company_name, company.company_city, count(projects.project_id), MAX(notes.note_date) as 'most_recent_note_date'
--FROM company
--LEFT OUTER JOIN projects ON projects.company_id = company.company_id
--LEFT OUTER JOIN notes ON notes.project_id = projects.project_id
--GROUP BY company.company_id, company.company_name, company.company_city

create table dbo.Company( -- Create Company Table
    CompanyID int primary key,
    CompanyName varchar(32),
    CompanyCity varchar(32)
);
create table dbo.Project( -- Create Project Table
    ProjectID int primary key,
    CompanyID int,
    ProjectName varchar(32)
);
create table dbo.Note( -- Create Note Table
    NoteID int primary key,
    ProjectID int,
    NoteDate datetime,
    NoteAuthor varchar(32),
    NoteText varchar(max)
);
insert into dbo.Company -- Insert test data to Company table
select 1,'ABC Corp','Some Town' union all
select 2,'ACME Inc','Another Town';
insert into dbo.Project -- Insert test data to Project table
select 1,1,'Simple Server Install' union all
select 2,1,'SAP Install' union all
select 3,2,'SQL Server Cluster Install' union all
select 4,2,'AXAPTA Install';
insert into dbo.Note -- Insert test data to Note table
select 1,1,getdate() - 3, 'Tom','Missing power cables for server' union all
select 2,1,getdate() - 2, 'Tom','Server cables arrived over-night' union all
select 3,2,getdate() - 4, 'Joe','SAP Install going great' union all
select 4,2,getdate(), 'Joe','New server up, creating reporting database for SAP system' union all
select 5,3,getdate() - 5, 'Sam','Having problems with cluster install' union all
select 6,3,getdate() - 3, 'Sam','Found the problem, disk controller not on HAL' union all
select 7,4,getdate() - 1, 'Tom','AXAPTA install now in progress, Cluster working great.  Thanks Sam' union all
select 8,4,getdate(), 'Tom','AXAPTA install complete, users loading configuration data';
select -- Verify data in Company table
    *
from
    dbo.Company;
select -- Verify data in Project table
    *
from
    dbo.Project;
select -- Verify data in Note table
    *
from
    dbo.Note;

SELECT
    Company.CompanyID,
    Company.CompanyName,
    Company.CompanyCity,
    count(Project.ProjectID) as ProjectCount,
    MAX(Note.NoteDate) as LatestNoteDate
FROM
    dbo.Company
    LEFT OUTER JOIN dbo.Project
        ON Project.CompanyID = Company.CompanyID
    LEFT OUTER JOIN dbo.Note
        ON Note.ProjectID = Project.ProjectID
GROUP BY
    Company.CompanyID,
    Company.CompanyName,
    Company.CompanyCity;
--
-- Started work on a CTE based solution, but stopped.  Meant to cut this part out but missed it
--
-- )
-- select
--    CompanyID,
--    CompanyName,
--    CompanyCity,
--    ProjectCount,
--    LatestNoteDate;
--

drop table dbo.Company; -- drop test table
drop table dbo.Project; -- drop test table
drop table dbo.Note; -- drop test table