Search code examples
sqlgoogle-bigquerysql-timestamp

How To Calculate Total Time In SQL When Column Is Less Than 0

I have a table in SQL that looks like the screenshot below and I am trying to calculate the total number of hours or days that the StockLevel column is less than or equal to 0.

enter image description here

I tried to subtract the time when the item is <= 0 from the time when it is greater than 0, then SUM up all the hours, but I have not been able to.

I want to be able to add the total hours in a month that an item was less than 0 maybe by subtracting the timestamps.

DATE_DIFF((CURRENT_DATE), MAX(DATE(ledger.EventTimeStamp)), DAY) AS Days_OutOfStock,

Thanks!

Solution

  • You can use the LAG function to find the stock level for every period:

    SELECT ItemID, StockLevel, EventTimeStamp AS lo,
       LEAD(EventTimeStamp,1) OVER (PARTITION BY itemId ORDER BY EventTimeStamp) AS hi

    That should give you:

    ItemID StockLevel lo               hi
536    205        2019-11-05 10:01 null
536    206        2019-11-05 09:37 2019-11-05 10:01

    You can then use that output to find the negative stock levels and the amount of time

    SELECT ItemId, DATEDIFF(HOUR,hi,lo) AS p
  FROM (
        SELECT ItemID, StockLevel, EventTimeStamp AS lo,
          LEAD(EventTimeStamp,1) OVER (PARTITION BY itemId ORDER BY EventTimeStamp) AS hi
  )
 WHERE StockLevel < 0

    You should be able to SUM the hours by month, by ItemID