I have a table consisting of Entity_Id, Date_of_Modification, Previous_State, and New_State for tickets we are working on.

I have figured out the query for calculating the total amount of time already spent by a ticket.
I also have figured out the time spent by the ticket on 'internal review' state because we want the time spent apart from this state and have written a query to calculate the remaining time.
-------query to find total time remaining for a ticket apart from internal review---------

 SELECT M.TotalTime - N.IRTotalTime AS RemainingHours
    FROM

----------query to find total time spent on a ticket---------

(SELECT SUM(B.Diff) AS TotalTime
    FROM
        (SELECT 
            A.Modification_Id,
            A.Date_of_Modification, 
            A.Previous_State, 
            A.State AS NewState, 
            DATEDIFF(DAY, LAG(Date_of_Modification) OVER (ORDER BY Date_of_Modification), Date_of_Modification)
                AS Diff
            FROM 
                (SELECT 
                    Modification_Id,
                    Date_of_Modification, 
                    Previous_State, 
                    State 
                FROM Book2 
                )AS A)
            AS B) AS M
    ,

----------query to find total time spent on internal review---------

(SELECT SUM(B.Diff) AS IRTotalTime
    FROM
        (SELECT 
            A.Modification_Id,
            A.Date_of_Modification, 
            A.Previous_State, 
            A.State AS NewState,    
            DATEDIFF(DAY, LAG(Date_of_Modification) OVER (ORDER BY Date_of_Modification), Date_of_Modification) AS Diff  
            FROM 
                (SELECT 
                    Modification_Id,
                    Date_of_Modification, 
                    Previous_State, 
                    State 
                FROM Book2
                    WHERE Previous_State = 'Internal Review' AND State <> 'Internal Review'

                    UNION 

                SELECT 
                    Modification_Id,
                    Date_of_Modification, 
                    Previous_State, 
                    State
                FROM Book2
                    WHERE Previous_State = 'Internal Review' AND State = 'Internal Review'
                ) AS A 
            ) AS B 
        WHERE B.Previous_State = 'Internal Review' AND B.NewState <> 'Internal Review') AS N 

But this query for some reason is only for for case when I specify the ticket number (i.e. Entity_Id). It is not working when I run it over the entire table. So I thought if we could use a loop to get the total remaining time of individual tickets. But I am having difficulty running that query through a loop and getting the Entity_Id displayed for each calculation on the tickets.

When I run the query I get the value 55 which might be the total remaining time. But I want the total remaining time for individual tickets like:

Thank you

Update: I used PARTITION BY Entity_Id and got the required total time and Internal Review time of individual tickets and saved the result in separate tables.
I now need to subtract the value of time of 2nd table from 1st table. There are rows that have NULL value in the time spent column in some of the rows of both the table.

Table A (Total time spent):

Table A (Time spent in Internal Review):

Thanks

CodePudding user response：

Try adding a PARTITION BY in your lag functions, like so:

DATEDIFF(DAY, LAG(Date_of_Modification) OVER (PARTITION BY Entity_Id ORDER BY Date_of_Modification), Date_of_Modification)

CodePudding user response：

Assuming all your assumptions are correct, this is simple aggregation.

Note: I'm only using a CTE term (PartialResult) to show where you can place any intermediate query (similar to a derived table) which generates the differences in your question. If it's a base table, just replace that entirely with a reference to the base table and remove the CTE term, if you wish.

I suspect there's more to the question with a better approach to a complete solution.

I just picked MySQL to show the test case. This is standard SQL.

WITH PartialResult AS (
        SELECT * FROM booksX
     )
SELECT Entity_Id, SUM(tdiff) AS total
  FROM PartialResult
 GROUP BY Entity_Id
 ORDER BY Entity_Id
;

The result:

 ----------- ------- 
| Entity_Id | total |
 ----------- ------- 
|         1 |     1 |
|         2 |    15 |
|         3 |     9 |
 ----------- ------- 

The setup:

CREATE TABLE booksX (
   Entity_Id             int
 , Date_of_Modification  date
 , Previous_State        varchar(20)
 , New_State             varchar(20)
 , tdiff                 int
);

INSERT INTO booksX VALUES
  ( 1, '2020/3/19', 'Internal Review', 'Done'           , 1)
, ( 1, '2020/3/18', 'Open'           , 'Internal Review', 0)
, ( 2, '2020/3/25', 'Internal Review', 'Done'           , 12)
, ( 2, '2020/3/13', 'Internal Review', 'Internal Review', 3)
, ( 2, '2020/3/10', 'Open'           , 'Internal Review', 0)
, ( 3, '2020/3/19', 'Internal Review', 'Done'           , 9)
, ( 3, '2020/3/10', 'Open'           , 'Internal Review', 0)
;