CODEWITHSUNDEEP
sqlgoogle-bigquerysql-timestamp
Gidi9
Gidi9

Reputation: 222

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!

Upvotes: 0

Views: 180

Answers (1)

SQL Hacks
SQL Hacks

Reputation: 1332

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

Upvotes: 2

Related Questions