15,919,341 members
5.00/5 (1 vote)
See more:
I'm trying to plot data on an ssrs chart. My x-axis will always have the values 1 through 31 plotted.

The issue with my data is that not every day has a sale. Example below:
```|Day | Quantity |
1  |    20    |
2  |    40    |
3  |   null   |
4  |    60    |```

What I'm trying to accomplish is below:
```|Day | Quantity |
| 1  |  20      |
| 2  |  40      |
| 3  |  40*     |
| 4  |  60      |```

What I have tried:

```UPDATE m
SET m.CMQty = CASE WHEN m.SkuNbr = t.ChildSku and m.Day = t.day THEN  t.QTY
WHEN m.SkuNbr = t.ChildSKU and m.day <> t.day THEN 0
end
FROM #MonthData2 m
left join #transform t
on m.SkuNbr = t.ChildSKU
and m.day = t.day
and t.MonthRank = 1 ```
Posted
Updated 28-Mar-17 17:23pm
RossMW 28-Mar-17 21:38pm
Where does the 40* come from? Normally you just use isnull(Quantity,0)

## Solution 1

Here is an example inspired by Calculating simple running totals in SQL Server[^] . I think your example of expected output is wrong. It should be 20, 60, 60*, 120

SQL
```DECLARE @RunTotalTestData TABLE  (
id    int not null identity(1,1) primary key,
value int null
);

INSERT INTO @RunTotalTestData (value) VALUES (1);
INSERT INTO @RunTotalTestData (value) VALUES (2);
INSERT INTO @RunTotalTestData (value) VALUES (4);
INSERT INTO @RunTotalTestData (value) VALUES (7);
INSERT INTO @RunTotalTestData (value) VALUES (9);
INSERT INTO @RunTotalTestData (value) VALUES (12);
INSERT INTO @RunTotalTestData (value) VALUES (NULL);
INSERT INTO @RunTotalTestData (value) VALUES (16);
INSERT INTO @RunTotalTestData (value) VALUES (22);
INSERT INTO @RunTotalTestData (value) VALUES (42);
INSERT INTO @RunTotalTestData (value) VALUES (57);
INSERT INTO @RunTotalTestData (value) VALUES (NULL);
INSERT INTO @RunTotalTestData (value) VALUES (59);
INSERT INTO @RunTotalTestData (value) VALUES (60);

;WITH temp AS(
SELECT a.id, a.value, SUM(b.Value) 'Sum'
FROM   @RunTotalTestData a,
@RunTotalTestData b
WHERE b.id <= a.id
GROUP BY a.id, a.value

)
SELECT id,
CASE
WHEN value is NULL THEN CONVERT(VARCHAR(5),[Sum]) +'*'
ELSE  CONVERT(VARCHAR(5),[Sum])
END 'Quantity'

FROM temp
ORDER BY id```

Output:
```id	Quantity
1	1
2	3
3	7
4	14
5	23
6	35
7	35*
8	51
9	73
10	115
11	172
12	172*
13	231
14	291```