1

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?

1
  • 2
    What have you tried? Have you looked up any of the available window functions, identified one that might meet your query requirements, and tried anything on your own? Sep 1, 2015 at 22:51

3 Answers 3

-1
create table #jobhistory (JobOrderID int, dateAdded datetime, Status varchar(10))
go
insert into #jobHistory (JobOrderID, dateAdded, Status)
select 4909, '2015-08-26 18:15:07.527', 'OPEN' union all
select 4909, '2015-08-28 13:35:38.997', 'CLOSE' union all
select 4909, '2015-08-31 12:16:29.787', 'OPEN' union all
select 4910, '2015-08-27 12:16:42.72 ', 'OPEN' union all
select 4910, '2015-08-28 17:04:43.617', 'CLOSE' union all
select 4910, '2015-08-31 17:01:27.337', 'OPEN' union all
select 4911, '2015-08-27 16:08:39.467', 'OPEN';
go
; with c as(
select jobOrderID, dateAdded, Status, rank() over (partition by jobOrderID order by dateAdded asc) as rnk 
from #jobHistory 
)
select c.jobOrderID, OpenDate=convert(char(10), c.dateAdded, 120), CloseDate=convert(char(10), c2.dateAdded, 120)
from c
left join c as c2
on c.jobOrderID = c2.jobOrderID
and c.Status='OPEN'
and c2.Status='Close'
and c2.rnk = c.rnk+1
where c.Status='Open';
1
  • 4
    Code-only answers can be OK, but you're more likely to attract up-votes from the community if the answer includes some explanation and analysis. Such answers are more likely to benefit future visitors.
    – Paul White
    Sep 2, 2015 at 4:57
0

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 > 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 > opened.dateAdded

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

Ste

0

This solution uses two CTEs to identify your open and close records and then joins the two datasets together. Enjoy

    --Build the test tables (using BIT instead of OPEN / CLOSE

    IF OBJECT_ID('JobHistory','U') IS NULL
    BEGIN
        CREATE TABLE JobHistory
        (
              jobOrderId INT
            , dateAdded DATETIME
            , [Status] BIT NOT NULL DEFAULT 1
        );

        INSERT JobHistory
        VALUES(4909, '2015-08-26 18:15:07.527', 1)
            , (4909, '2015-08-28 13:35:38.997', 0)
            , (4909, '2015-08-31 12:16:29.787', 1)
            , (4910, '2015-08-27 12:16:42.72 ', 1)
            , (4910, '2015-08-28 17:04:43.617', 0)
            , (4910, '2015-08-31 17:01:27.337', 1)
            , (4911, '2015-08-27 16:08:39.467', 1);
    END

    --Verify data looks the same as the proposed scenario
    SELECT jobOrderId, dateAdded, CASE [STATUS] WHEN 1 THEN 'OPEN' ELSE 'CLOSE' END AS [Status]
    FROM JobHistory;

    --Use windowing functions to gather OPEN and CLOSE records
    WITH cteOpen AS
    (
        SELECT jobOrderId, dateAdded AS opendate
        FROM JobHistory
        WHERE Status=1
    ), 
    cteClosed AS
    (
        SELECT jobOrderId, dateAdded AS closedate
        FROM JobHistory
        WHERE Status=0
    )
    SELECT o.jobOrderId, o.opendate, c.closedate
    FROM cteOpen o
        LEFT JOIN cteClosed c ON c.jobOrderId = o.jobOrderId
            AND c.closedate > o.opendate --> WILL ONLY PRODUCE A CLOSE RECORD IF IT'S GREATER THAN AN OPEN DATE
    ORDER BY o.jobOrderId;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.