DynDeveloper.com

DynDeveloper.com

Sign Up Now! Log In
RachelW 11/30/2017 4:09:34 PM

CROSS APPLY example

* This article, and all our great SQL (General) documentation, Is available on the SQL (General) menu

Ok, get your thinking caps on. The customer requirement is this:

We have a JOB table and a JOBSTATUS table, display the Job and the latest Status

Here is our data:

declare @job as table(Job varchar(15))
insert into @job (Job) values ('Job1')
insert into @job (Job) values ('Job2')
 
declare @log as table (RowID int identity, Job varchar(15), Status varchar(10))
insert into @log(job, status) values ('Job1','Starting')
insert into @log(job, status) values ('Job1','Step 1')
insert into @log(job, status) values ('Job1','Step 2')
insert into @log(job, status) values ('Job2','Starting')
insert into @log(job, status) values ('Job2','Step 1')
insert into @log(job, status) values ('Job2','Step 2')
insert into @log(job, status) values ('Job2','Step 3')
insert into @log(job, status) values ('Job2','Done')

Given this, how would you write a query to return this?

See my answer below... but I'd like to hear yours

Version: Unknown or N/A
Section: SQL Scripts