Question: I have temp table that I've populated with a running total. I used SQL Server windowing functions. The data in my temp table is in the following format: 

```
|Day  | Sku  Nbr | CMQTY  | 
| 1   |  f45     |   0    |
| 2   |  f45     |   2    | 
| 3   |  f45     |   0    | 
| 4   |  f45     |   7    | 
| 5   |  f45     |   0    | 
| 6   |  f45     |   0    | 
| 7   |  f45     |   0    | 
| 8   |  f45     |   13   | 
| 9   |  f45     |  15    | 
| 10  |  f45     |  21    |

```

I would like to manipulate the data so that it displays like this: 

```
|Day| Sku Nbr | CMQTY | 
| 1 |  f45    | 0     | 
| 2 |  f45    | 2     | 
| 3 |  f45    | 2     | 
| 4 |  f45    | 7     | 
| 5   |  f45  |   7   | 
| 6   |  f45  |   7   | 
| 7   |  f45  |   7   | 
| 8   |  f45  |   13  | 
| 9   |  f45  |  15   | 
| 10  |  f45  |  21   |

```

I've tried using a lag function but there are issues when I have multiple days, in a row, with a 0 CMQTY. I've also tried using CASE WHEN logic but am failing.

Answer: 
You can use row\_number as below

```
;with cte as (
select *, sm = sum(case when cmqty>0 then 1 else 0 end) over (order by [day]) from #yoursum
)
select *, sum(cmqty) over(partition by sm order by [day]) from cte

```

Your table structure

```
create table #yoursum ([day] int, sku_nbr varchar(10), CMQTY int)

insert into #yoursum 
([Day]  , Sku_Nbr , CMQTY  )  values 
 ( 1   ,'f45',   0  )
,( 2   ,'f45',   2  )
,( 3   ,'f45',   0  )
,( 4   ,'f45',   7  )
,( 5   ,'f45',   0  )
,( 6   ,'f45',   0  )
,( 7   ,'f45',   0  )
,( 8   ,'f45',   13 )
,( 9   ,'f45',  15  )
,( 10  ,'f45',  21  )

```
For fun, another approach. First for some sample data:

```
IF OBJECT_ID('tempdb..#t1') IS NOT NULL DROP TABLE #t1;

CREATE TABLE #t1
(
  [day]     int        NOT NULL,
  [Sku Nbr] varchar(5) NOT NULL, 
  CMQTY     int        NOT NULL,
  CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED([day] ASC)
);

INSERT #t1 VALUES
(1 , 'f45', 0),
(2 , 'f45', 2),
(3 , 'f45', 0),
(4 , 'f45', 7),
(5 , 'f45', 0),
(6 , 'f45', 0),
(7 , 'f45', 0),
(8 , 'f45', 13), 
(9 , 'f45', 15), 
(10, 'f45', 21);

```

And the solution:

```
DECLARE @RunningTotal int = 0;

UPDATE #t1
SET @RunningTotal = CMQTY = IIF(CMQTY = 0, @RunningTotal, CMQTY)
FROM #t1 WITH (TABLOCKX)
OPTION (MAXDOP 1);

```

Results:

```
day  Sku Nbr CMQTY
---- ------- ------
1    f45     0
2    f45     2
3    f45     2
4    f45     7
5    f45     7
6    f45     7
7    f45     7
8    f45     13
9    f45     15
10   f45     21

```

This approach is referred to the local updateable variable or Quirky Update. You can read more about it here: <http://www.sqlservercentral.com/articles/T-SQL/68467/>