Question

Source:https://stackoverflow.com/questions/70465767/sum-values-by-sequence-number-and-group-by-flag

We have a list with sequence numbers. The sequence will break, then begin again.

As you can see below, number 4 is missing under SalesOrderLine.

SalesOrder SalesOrderLine MStockCode MPrice MBomFlag

000000000182181 1 901337 0.00000 P

000000000182181 2 901335 2476.90000 C

000000000182181 3 340151 0.00000 C

000000000182181 5 900894 0.00000 P

000000000182181 6 400379 0.00000 C

000000000182181 7 900570 600.90000 C

What I'm looking to do is to summarize the MPrice field by a consecutive number sequence, then use the MBomFlag field to pick the"parent".

This would be the expected result of the above data. Any grouping will be done on the MBomFlag field if the value =P.

SalesOrder SalesOrderLine MStockCode MPrice MBomFlag

000000000182181 1 901337 2476.90000 P

000000000182181 5 900894 600.90000 P

What would be the best way to attack this? I'm trying to figure out something using RANK(),ROW_NUMBER(),LEAD, and LAG but not having much success.

Here is the source data:

CREATE TABLE #SalesOrder (

SalesOrder NVARCHAR(20),

SalesOrderLine INT,

MStockCode INT,

MPrice DECIMAL(18,2),

MBomFlag VARCHAR(1))

INSERT INTO #SalesOrder (SalesOrder, SalesOrderLine, MStockCode, MPrice, MBomFlag)

SELECT '000000000182181', 1, '901337', 0.00000, 'P'

UNION

SELECT '000000000182181', 2, '901335', 2476.90000, 'C'

UNION

SELECT '000000000182181', 3, '340151', 0.00000, 'C'

UNION

SELECT '000000000182181', 5, '900894', 0.00000, 'P'

UNION

SELECT '000000000182181', 6, '400379', 0.00000, 'C'

UNION

SELECT '000000000182181', 7, '900570', 2600.90000, 'C'

SELECT *

FROM #SalesOrder

DROP TABLE #SalesOrder

Answer

The data is ordered by SalesOrderLine. You create a new group each time when the difference between the current SalesOrderLine value and the next one is not 1; in each group, find the record where MbomFlag is P and change its Mprice value to the sum of Mprice values in the current group. To implement this in SQL, you need to first create a marker column using the window function, group rows by this marker column, and get the final result using case statement. A common alternative is to fetch the original data out of the database and process it in Python or SPL. SPL, the open-source Java package, is easy to be integrated into a Java program and generates much simpler code. It handles the computation with only three lines of code:

	A
1	=MSSQL.query@x("select * from Sales order by 2")
2	=A1.group@i(#2-#2[-1]!=1)
3	=A2.conj(~.select(#5=="P").run(#4=A2.~.sum(#4)))

 

View SPL source code.