Next positive date in SQL

Question

p_date	Qty	Available	Type
11/04/2021	-1	2	Debit
11/08/2021	-1	1	Debit
11/09/2021	-1	0	Debit
11/15/2021	-1	-1	Debit
11/17/2021	-5	-6	Debit
11/18/2021	-50	-56	Debit
11/18/2021	-3	-59	Debit
11/18/2021	-1	-60	Debit
11/20/2021	10	-50	Credit
11/21/2021	100	50	Credit
11/21/2021	-1	49	Debit
11/24/2021	-62	-13	Debit
11/26/2021	-4	-17	Debit
11/30/2021	-1	-18	Debit

I have a table like above where the 'Qty' could be positive number or negative number depending on whether the 'Type' column is 'Debit' or 'Credit'.

I want to write a SQL query to add a extra column to the table like below to show next positive p_date when the 'Available' column will become positive.
Note: when the 'Available' already column is positive number, the 'Next Positive p_date' column will show the 'p_date' column.

p_date	Qty	Available	Type	Next Positive p_date
11/04/2021	-1	2	Debit	11/04/2021
11/08/2021	-1	1	Debit	11/08/2021
11/09/2021	-1	0	Debit	11/09/2021
11/15/2021	-1	-1	Debit	11/21/2021
11/17/2021	-5	-6	Debit	11/21/2021
11/18/2021	-50	-56	Debit	11/21/2021
11/18/2021	-3	-59	Debit	11/21/2021
11/18/2021	-1	-60	Debit	11/21/2021
11/20/2021	10	-50	Credit	11/21/2021
11/21/2021	100	50	Credit	11/21/2021
11/21/2021	-1	49	Debit	11/21/2021
11/24/2021	-62	-13	Debit	12/05/2021
11/26/2021	-4	-17	Debit	12/05/2021
11/30/2021	-1	-18	Debit	12/05/2021
12/05/2021	80	62	Credit	12/05/2021

Please do not spam tags, add only the tag of the DBMS you actually use, because they all different. — astentx, Nov 06 '21 at 06:10
Does this answer your question? [How to make LAG() ignore NULLS in SQL Server?](https://stackoverflow.com/questions/60105702/how-to-make-lag-ignore-nulls-in-sql-server) — Serg, Nov 06 '21 at 07:16
@rain_maker Which specific RDBMS are you using(MySQL/Postgresql/Snowflake)? — Lukasz Szozda, Nov 06 '21 at 15:13
@Lukasz is it possible to keep track of the quantities that are used. Like below. https://stackoverflow.com/questions/69865551/next-positive-date-in-sql-with-keeping-tracking-of-quantity?noredirect=1#comment123499511_69865551 — rain_maker, Nov 06 '21 at 23:44

Lukasz Szozda · Accepted Answer · 2021-11-06T06:13:44.173

It could be achieved with LEAD windowed function:

SELECT *, CASE WHEN Available >= 0 THEN p_date
               ELSE LEAD(CASE WHEN Available > 0 THEN p_date END) IGNORE NULLS
                    OVER(ORDER BY p_date)
          END next_p_date
FROM tab
ORdER BY p_date;

How it works: CASE expression nullifies p_date if Available is lower than 0 and IGNORE NULLS skips that p_date(effectively finding the first p_date with positive available)

Next positive date in SQL

1 Answers1