I have the next data: TABLE_A

RegisteredDate	Quantity
2022-03-01 13:00	100
2022-03-01 13:10	20
2022-03-01 13:20	-80
2022-03-01 13:30	-40
2022-03-02 09:00	10
2022-03-02 22:00	-5
2022-03-03 02:00	-5
2022-03-03 03:00	25
2022-03-03 03:20	-10

if i add cumulative column

select RegisteredDate, Quantity, sum(Quantity) over ( order by RegisteredDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Summary
from TABLE_A

RegisteredDate	Quantity	Summary
2022-03-01 13:00	100	100
2022-03-01 13:10	20	120
2022-03-01 13:20	-80	40
2022-03-01 13:30	-40	0
2022-03-02 09:00	10	10
2022-03-02 22:00	-5	5
2022-03-03 02:00	-5	0
2022-03-03 03:00	25	25
2022-03-03 03:20	-10	15

Is there a way to get the following result with a query?

RegisteredDate	Quantity	Summary
2022-03-03 03:00	25	25
2022-03-03 03:20	-10	15

This result is the last records after the last zero.

EDIT:

Really for the solve this problem i need the: 2022-03-03 03:00 is the first date of the last records after the last zero.

CodePudding user response：

Use a CTE that returns the summary column and NOT EXISTS to filter out the rows that you don't need:

WITH cte AS (SELECT *, SUM(Quantity) OVER (ORDER BY RegisteredDate) Summary FROM TABLE_A)
SELECT c1.*
FROM cte c1
WHERE NOT EXISTS (
    SELECT 1
    FROM cte c2 WHERE c2.RegisteredDate >= c1.RegisteredDate AND c2.Summary = 0 
  )
ORDER BY c1.RegisteredDate;

There is no need for ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW in the OVER clause of the window function, because this is the default behavior.

See the demo.

CodePudding user response：

Try this:

with u as
(select RegisteredDate, 
Quantity, 
sum(Quantity) over (order by RegisteredDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Summary
from TABLE_A)
select * from u
where RegisteredDate >= all(select RegisteredDate from u where Summary = 0)
and Summary <> 0;

Fiddle

Basically what you want is for RegisteredDate to be >= all RegisteredDatess where Summary = 0, and you want Summary <> 0.

CodePudding user response：

You can try to use SUM aggregate window function to calculation grp column which part represent to last value accumulated.

Query 1:

WITH cte AS
(
    SELECT RegisteredDate, 
        Quantity, 
        sum(Quantity) over (order by RegisteredDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Summary
    FROM TABLE_A
), cte2 AS (
   SELECT *,
          SUM(CASE WHEN Summary = 0 THEN 1 ELSE 0 END) OVER(order by RegisteredDate desc) grp
   FROM  cte
)
SELECT RegisteredDate,
       Quantity
FROM cte2
WHERE grp = 0
ORDER BY RegisteredDate

Results:

|       RegisteredDate | Quantity |
|----------------------|----------|
| 2022-03-03T03:00:00Z |       25 |
| 2022-03-03T03:20:00Z |      -10 |

CodePudding user response：

When using window functions, it is necessary to take into account that RegisteredDate column is not unique in TABLE_A, so ordering only by RegisteredDate column is not enough to get a stable result on the same dataset.

With A As (
Select ROW_NUMBER() Over (Order by RegisteredDate, Quantity) As ID, RegisteredDate, Quantity       
From TABLE_A),
B As (
Select A.*, SUM(Quantity) Over (Order by ID) As Summary
From A)
Select Top 1 * 
From B
Where ID > (Select MAX(ID) From B Where Summary=0)

ID	RegisteredDate	Quantity	Summary
8	2022-03-03 03:00	25	25