# SUM Values by Sequence Number and Group by Flag

Question

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