Tracking the amount of time a number is greater than 0 in SQL

Question

I'm trying to figure out a way to keep track of how long a part has a QTY greater than 0. I've setup a table in SQL Server like this.

CREATE TABLE [dbo].[Levels] (
    RecordID    INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Timestamp   DATETIME DEFAULT(current_timestamp) NOT NULL,
    Part        VARCHAR(10) NOT NULL,
    QTY         INT NOT NULL
)

With this I only need to use the Part and QTY column when I'm doing an insert statement. I want all of the parts that I'm keeping track of to start off at 0, then to have some QTY then to go back to 0. I've used the following queries to populate the table for now, with a small delay inbetween each pair.

INSERT INTO [dbo].[Levels] (Part, QTY) VALUES('PART1234', 0)
INSERT INTO [dbo].[Levels] (Part, QTY) VALUES('ITEM5678', 0)

and a few seconds later.

INSERT INTO [dbo].[Levels] (Part, QTY) VALUES('PART1234', 5)
INSERT INTO [dbo].[Levels] (Part, QTY) VALUES('ITEM5678', 19)

and then a few more seconds.

INSERT INTO [dbo].[Levels] (Part, QTY) VALUES('PART1234', 0)
INSERT INTO [dbo].[Levels] (Part, QTY) VALUES('ITEM5678', 0)

I now have this data in my table.

  RecordID    Timestamp               Part       QTY
----------- ----------------------- ---------- -----------
1           2017-05-23 13:56:57.123 PART1234   0
2           2017-05-23 13:56:57.123 ITEM5678   0
3           2017-05-23 13:57:00.857 PART1234   5
4           2017-05-23 13:57:00.890 ITEM5678   19
5           2017-05-23 13:57:02.827 PART1234   0
6           2017-05-23 13:57:02.827 ITEM5678   0

Now that I have all of the data I need I need to figure out how to see how long an item has had a QTY greater than 0.

For example, I could calculate that PART1234 was in stock for 1.97 seconds, but how can I get this from a single SQL Query in the following format so I can see all of the parts and a total time in stock even if they are going in and out of stock in this table?

Part     InStockPeriod
-------- ----------------
PART1234 00:00:01.9700000
ITEM5678 00:00:01.9366667

Thank you,

With a little tweaking of Kannan Kandasamy's answer I arrived at this. While it does not return in a time format, it does return the seconds that an item was in stock properly and will update based on the CURRENT_TIMESTAMP if the item is currently in stock.

WITH Thing as (
SELECT *,
        [TimeDiff] = CONVERT(TIME, 
            CASE
                WHEN LEAD(TIMESTAMP, 1, [TimeStamp]) OVER (PARTITION BY Part ORDER BY [TimeStamp]) <> [Timestamp] THEN
                    lead(TIMESTAMP, 1, [TimeStamp]) OVER (PARTITION BY Part ORDER BY [TimeStamp]) - [Timestamp]
                ELSE
                    CURRENT_TIMESTAMP - [TimeStamp]
            END)
    FROM [Levels])
SELECT   Part, 
         sum( DATEPART(SECOND, [TimeDiff]) + 60 * 
              DATEPART(MINUTE, [TimeDiff]) + 3600 * 
              DATEPART(HOUR, [TimeDiff] ) 
            ) as 'TotalTime' 
FROM Thing WHERE QTY > 0
GROUP BY Part

With the sample data it returns this.

Part       TotalTime
---------- -----------
ITEM5678   1
PART1234   1

And if I add a record saying ITEM5678 is in stock again.

INSERT INTO [dbo].[Levels] (Part, QTY) VALUES('ITEM5678',1)

Then the query will start counting up in seconds as long as the most recent record for that item is greater than 0.

Part       TotalTime
---------- -----------
ITEM5678   20
PART1234   1

Thank you very much for your help!

Answer 1

You can use lead and get time difference as below:

select [Part], --sum(TimeDiff) as InStockPeriod 
    sum(datepart(ms,timediff)/60.00) InStockSeconds
 from (
    select *,
        [TimeDiff] = convert(time, lead(timestamp, 1, [TimeStamp]) over (partition by Part order by [TimeStamp])- [Timestamp])
        --, lead(timestamp) over (partition by Part order by [TimeStamp]) 
    from levels
) a
group by [part]

But it will get data into seconds we need to convert it into time format