By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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
| SalesOrder | SalesOrderLine | MStockCode | MPrice | MBomFlag |
|---|---|---|---|---|
| 000000000182181 | 1 | 901337 | 0.00 | P |
| 000000000182181 | 2 | 901335 | 2476.90 | C |
| 000000000182181 | 3 | 340151 | 0.00 | C |
| 000000000182181 | 5 | 900894 | 0.00 | P |
| 000000000182181 | 6 | 400379 | 0.00 | C |
| 000000000182181 | 7 | 900570 | 2600.90 | C |
with u as
(select *, lag(SalesOrderLine) over (order by SalesOrderLine) as previousSOL
from #SalesOrder),
v as
(select *, sum(case when SalesOrderLine = PreviousSOL+ 1 then 0 else 1 end)
over (order by SalesOrderLine rows unbounded preceding) as jumps
from u
)
select min(case when MBomFlag = 'P' then SalesOrder end) as SalesOrder,
min(case when MBomFlag = 'P' then SalesOrderLine end) as SalesOrderLine,
min(case when MBomFlag = 'P' then MStockCode end) as MStockCode,
sum(MPrice) as Mprice,
'P' as MBomFlag
from v
group by jumps;
| SalesOrder | SalesOrderLine | MStockCode | Mprice | MBomFlag |
|---|---|---|---|---|
| 000000000182181 | 1 | 901337 | 2476.90 | P |
| 000000000182181 | 5 | 900894 | 2600.90 | P |
Warning: Null value is eliminated by an aggregate or other SET operation.