How to subtract consecutive rows from different tables in MS SQL Server 2012?

Question

I have a main table (table0 from database0) in SQL Server 2012 where I store information about job objects. Each job has unique ID and has only one entry (row) in the main table. Each modification on job object makes a new row in different table from the same database (table1 from databese0). After some time, agent moves rows from table1 (from database0) into another table in another database (table2 from database1). Basically I’m logging each change on the jobs object into audit table and after some time agent move entries from audit table to another audit table in different database.

I made a query:

select t0.Job_ID,
t1.TimeStamp, t1.Status, t1.Change,
t2.TimeStamp, t2.Status, t2.Change

from [database0].dbo.[Table0] as t0

left outer join [database0].dbo.[Table1] as t1 on t1.Job_ID=t0.Job_ID
left outer join [database1].dbo.[Table2] as t2 on t2.Job_ID=t0.Job_ID

where t1.Status='Created' or t1.Change='StatusChange'
or t2.Status='Created' or t2.Change='StatusChange'

order by t0.Job_ID, t1.TimeStamp, t2.TimeStamp

Which returns data like this:

t0.JobID|t1.TimeStamp|t2.TimeStamp|t1.Status|t2.Status|t1.Change|t2.Change
--------|------------|------------|---------|---------|---------|---------
  Job1  |12:00:00.000|    Null    |   New   |   Null  | Created |   Null
  Job1  |12:10:00.000|    Null    |   Wait  |   Null  |St.Change|   Null
  Job1  |12:25:00.000|    Null    |   New   |   Null  |St.Change|   Null
  Job1  |    Null    |12:30:00.000|   Null  | InProgr.|   Null  |St.Change
  Job1  |    Null    |12:40:00.000|   Null  | Finished|   Null  |St.Change
--------|------------|------------|---------|---------|---------|---------
  Job2  |13:00:00.000|    Null    |   New   |   Null  | Created |   Null
  Job2  |    Null    |13:15:00.000|   Null  | InProgr.|   Null  |St.Change
  Job2  |    Null    |13:20:00.000|   Null  |Unfinish.|   Null  |St.Change

I need to measure how much time has each job spent in each status. So basically, for each job, I need to subtract TimeStamp consecutive rows (from the same or different table) from the moment the job has been created until one of the final status (Finished/Unfinished) and put the results in adequate rows. I need to get the results like this:

 Job_ID |  New  |  Wait  |  InProg.  | Total_Time | Final_Status
--------|-------|--------|-----------|------------|-------------
  Job1  |   15  |   15   |     10    |     40     |   Finished
  Job2  |   15  |   0    |     5     |     20     |  Unfinished

Example for Job1:

1. Job1 for example has been created at 12:00:00 and on next StatusChange it moved to status “Wait” at 12:10:00. So job1 has been in status “New” for 10 minutes.
2. Next StatusChange was at 12:25:00 to status “New” again. So the job1 has been in status “Wait” for 15 minutes.
3. Next StatusChange was at 12:30:00 to status “InProgress”. But this entry is another table (t2). So the job1 has been in status “New” again for 5 minutes (t2.TimeStamp – t1.TimeStamp), which needs to be added to the previous measure for status “New” (which is 10 minutes) and finally give us 15 minutes for status “New” in total.
4. And finally last StatusChange was at 12:40:00 to status “Finished”. So the job1 has been in status “InProgress” for 10 minutes.
5. Also I need to measure Total time which is the sum of times for each Job Status, which is 40 minutes for Job1 in this example. I also need to write the final status which is "Finished" for Job1.

I must not modify existing tables.

Can something like this be done using SQL queries and what is the most efficient way to do this?

Thanks in advance

SQL Code for Testing:

Create tables:

USE [databasename0] -- replace databasename0 name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t0](
[Job_ID] [nvarchar](64) NULL,
[Attribute1] [nvarchar](64) NULL,
[Attribute2] [nvarchar](64) NULL,
)
GO

USE [databasename0] -- replace databasename0 name
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t1](
[AuditTimeStamp] [datetime] NULL,
[Job_ID] [nvarchar](64) NULL,
[Status] [nvarchar](64) NULL,
[ChangeDescription] [nvarchar](64) NULL,
)
GO

USE [databasename1] -- replace databasename1 name with different database
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t2](
[AuditTimeStamp] [datetime] NULL,
[Job_ID] [nvarchar](64) NULL,
[Status] [nvarchar](64) NULL,
[ChangeDescription] [nvarchar](64) NULL,
)
GO

Insert into:

INSERT INTO [database0].[dbo].[t0] -- replace database0 name
       (Job_ID, Attribute1, Attribute2)
VALUES
       ('Job1','Test1','Test2'),
       ('Job2','Test3','Test4')
GO

INSERT INTO [database0].[dbo].[t1] --replace database0 name
       (AuditTimeStamp,Job_ID,Status,ChangeDescription)
VALUES
       ('2017-12-21 12:00:00.000','Job1','New','Created'),
       ('2017-12-21 12:10:00.000','Job1','Wait','StatusChange'),
       ('2017-12-21 12:11:00.000','Job1','Wait','Other'),
       ('2017-12-21 12:25:00.000','Job1','New','StatusChange'),
       ('2017-12-21 12:26:00.000','Job1','New','Other'),
       ('2017-12-21 13:00:00.000','Job2','New','Created')
GO

INSERT INTO [database1].[dbo].[t2] -- replace database1 name
       (AuditTimeStamp,Job_ID,Status,ChangeDescription)
VALUES
       ('2017-12-21 12:30:00.000','Job1','InProgress','StatusChange'),
       ('2017-12-21 12:31:00.000','Job1','InProgress','Other'),
       ('2017-12-21 12:40:00.000','Job1','Finished','StatusChange'),
       ('2017-12-21 13:15:00.000','Job2','InProgress','StatusChange'),
       ('2017-12-21 13:17:00.000','Job2','InProgress','Other'),
       ('2017-12-21 12:20:00.000','Job2','Unfinished','StatusChange')
GO

Answer 1

I expect this to have errors in it, but I can't test as the OP is yet to provide consumable sample data. I am more than happy to edit this answer once they have provided it though (InstantE, please reply to this answer once you have done so, so that I get a notification).

WITH Leads AS (
    SELECT T0.JobID,
           ISNULL(T1.[TimeStamp], T2.[TimeStamp]) AS [TimeStamp],
           LEAD(T1.[TimeStamp], T2.[TimeStamp]) OVER (PARTITION BY T0.JobId ORDER BY ISNULL(T1.[TimeStamp], T2.[TimeStamp]) ASc) AS NextTimeStamp,
           ISNULL(T1.[Status], T2.[Status]) AS [Status],
           LAST_VALUE(ISNULL(T1.[Status], T2.[Status])) OVER (PARTITION BY T0.JobID ORDER BY ISNULL(T1.[TimeStamp], T2.[TimeStamp])) AS FinalStatus
    FROM YourTables --I haven't included your JOIN's and WHERE here, you'll need to replace that
    )
SELECT JobID,
       SUM(CASE [Status] WHEN 'New' THEN DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp]) END) AS New,
       SUM(CASE [Status] WHEN 'Wait' THEN DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp]) END) AS Wait,
       SUM(CASE [Status] WHEN 'InProgr.' THEN DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp]) END) AS [InProgr.],
       SUM(DATEDIFF(MINUTE, [TimeStamp], [NextTimeStamp])) AS Total_time,
       FinalStatus
FROM Leads
GROUP BY JobID, FinalStatus;