Count of a column based on rolling sum

Database
Enthusiast

Count of a column based on rolling sum

Hi All,

We have a table in below format 







Material ID  Plant_ID Week NO XA
1234 700 W1 -40
1234 701 W2 -10
1234 702 W3 100
1234 700 W4 -150

Requirement is that we need to have 

a) Count of material having sum of column XA < 0 for Week 1 and Week 2 

b) Count of material having sum of column XA < 0 for Week 2 and Week 3 and so on…... 

so the end result should be like below 







Material ID  Plant_ID Week No XA WK1-2 Wk2-3 Wk3-4 Wk4-5
1234 700 W1 -40 2      
1234 701 W2 -10   0    
1234 702 W3 100     1  
1234 700 W4 -150       1
234 700 W1 -40