Select the rows on N and do SUM

Question

I have a table in my SQL Server database with the following contents in it:

Date | Amount ------------|---------- 2012-12-17 | 9.00 2012-12-18 | 8.00 2012-12-19 | 0.00 2012-12-20 | 1.50 2012-12-21 | 2.50 2012-12-22 | 0.00 2012-12-23 | 0.00 2012-12-24 | 0.00 2012-12-25 | 0.00 2012-12-26 | 4.00 2012-12-27 | 2.00 2012-12-28 | 7.00 

What I want to do is take 3 lines to choose from and SUM Amount . If the total SUM is 0 , then it should delete these 3 entries. Otherwise, he should just leave them alone and make the next 3 entries and make the same checks.

So, in this case, only the next three records should be deleted from the table, since they are the only ones where SUM will lead to 0 .

 2012-12-23 | 0.00 2012-12-24 | 0.00 2012-12-25 | 0.00 

How can I do it in SQL Server?

Answer 1

You can use ROW_NUMBER to create 3 groups of elements and a sum of calucalte.

 WITH cte AS ( SELECT *, rn = (ROW_NUMBER() OVER(ORDER BY [Date]) - 1) / 3 FROM #tab ), cte2 AS ( SELECT *, [sum] = SUM(Amount) OVER (PARTITION BY rn ORDER BY [Date]) FROM cte ) SELECT * FROM cte2 WHERE [sum] = 0; 

LiveDemo

And with DELETE :

 WITH cte AS ( SELECT *, rn = (ROW_NUMBER() OVER(ORDER BY [Date]) - 1) / 3 FROM #tab ), cte2 AS ( SELECT *, [sum] = SUM(Amount) OVER (PARTITION BY rn ORDER BY [Date]) FROM cte ) DELETE t FROM #tab t JOIN cte2 c ON t.[Date] = c.[Date] WHERE [sum] = 0; SELECT * FROM #tab; 

LiveDemo2

EDIT:

If your data may contain negative values, you can use:

 WITH cte AS ( SELECT *, rn = (ROW_NUMBER() OVER(ORDER BY [Date]) - 1) / 3 FROM #tab ), cte2 AS ( SELECT rn, [sum] = SUM(Amount) FROM cte GROUP BY rn ) SELECT c.* FROM cte c JOIN cte2 c2 ON c.rn = c2.rn WHERE [sum] = 0; 

LiveDemo3