'How can I select records from the last value accumulated

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 solution to this problem I need the: 2022-03-03 03:00 is the first date of the last records after the last zero.

The expected result

sqlsql-server


Solution 1:[1]

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 |

Solution 2:[2]

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.

Solution 3:[3]

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.

Solution 4:[4]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 D-Shih
Solution 2 forpas
Solution 3 Zakaria
Solution 4

Related Questions