SQL Server SUM (add) prices of accessories to products

Question

I need to sum up the prices of accessories to the main product. There is no link between the accessories and the correspondent product, however all accessories between two product belongs to the previous product (see side note).

SQL Server 2017

Input:

| No | Order | Type      | ProdNo | Price |     side note
--------------------------------------
|  1 | 20213 | Product   | 1320   | 200   |     + 0 + 20
|  2 | 20213 | Accessory | 823    | 0     |     acc. of 1320
|  3 | 20213 | Accessory | 836    | 20    |     acc. of 1320
|  4 | 20213 | Product   | 2680   | 300   |     + 0 + 0 + 0 + 0
|  5 | 20213 | Accessory | 231    | 0     |     acc. of 2680
|  6 | 20213 | Accessory | 536    | 0     |     acc. of 2680
|  7 | 20213 | Accessory | 23     | 0     |     acc. of 2680
|  8 | 20213 | Accessory | 361    | 0     |     acc. of 2680
|  9 | 20213 | Product   | 3320   | 50    |     + 10 + 15
| 10 | 20213 | Accessory | 328    | 10    |     acc. of 3320 
| 11 | 20213 | Accessory | 369    | 15    |     acc. of 3320

Output:

| No | Order | Type      | ProdNo | Price |  
--------------------------------------
|  1 | 20213 | Product   | 1320   | 220   |
|  4 | 20213 | Product   | 2680   | 300   |
|  9 | 20213 | Product   | 3320   | 75    |

Answer 1

I understand this as a kind of gaps-and-island problem. You could use window functions as follows to solve it:

select *
from (
    select no, order, type, prodNo, sum(price) over(partition by grp) price
    from (
        select
            t.*, 
            sum(case when type = 'Product' then 1 else 0 end) 
                over(partition by orderNo order by no) grp
        from mytable t
    ) t
) t
where type = 'Product'

The most inner query uses a window sum to define the groups of records. Everytime a product record is met, a new group starts. The intermediate query sums the prices in each group. Finally, the most outer query filters on product records only.