SQL Server 2012 window function I've the following table which holds status of jobs in a history table.

JobHistory

jobOrderId | dateAdded               | Status
-----------|-------------------------|-------
4909       | 2015-08-26 18:15:07.527 | OPEN
4909       | 2015-08-28 13:35:38.997 | CLOSE
4909       | 2015-08-31 12:16:29.787 | OPEN
4910       | 2015-08-27 12:16:42.72  | OPEN
4910       | 2015-08-28 17:04:43.617 | CLOSE
4910       | 2015-08-31 17:01:27.337 | OPEN
4911       | 2015-08-27 16:08:39.467 | OPEN


I would like to get the following output

jobOrderId | opendate   | closedate
-----------|------------|-----------
4909       | 2015-08-26 | 2015-08-28
4909       | 2015-08-31 | NULL
4910       | 2015-08-27 | 2015-08-28
4910       | 2015-08-31 | NULL
4911       | 2015-08-27 | NULL


Can any one suggest me how to get this kind of output using SQL Server 2012 window functions?
Providing that you'll always close a job before opening another under the same ID (although personally you should never have two seperate jobs running under the same ID without another unique identifier to distinguish (You've opened 4909 and 4910 twice))

if you join the table on itself distinguishing them between Open and Closed you can get it all in one short statement

select opened.jobOrderId as [Job Order ID],opened.dateadded as [opened date], closeed.dateadded as [closed date]
FROM JobHistory as opened
LEFT JOIN Jobhistory closeed on opened.jobOrderID = closeed.jobOrderID and closeed.status='Close' and closeed.dateadded &gt; opened.dateAdded
WHERE opened.status='OPEN' 


If you've got an actual unique identifier for the job I'd recommend joining on that rather than the JobOrderID column and you won't then need do the statement:

and closeed.dateadded &gt; opened.dateAdded


as that is simply there to ensure that you don't close something that was opened in the future

Ste