Next positive date in SQL-CodePudding

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

CodePudding user response：

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)