# Running Totals / SQL Temp Tables

**URL:** <https://forum.inductiveautomation.com/t/running-totals-sql-temp-tables/2676>\
**Category:** Ignition\
**Created:** [February 23, 2011, 2:15pm UTC](https://forum.inductiveautomation.com/t/running-totals-sql-temp-tables/2676 "2011-02-23T14:15:59Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![scicco](https://avatars.discourse-cdn.com/v4/letter/s/e0b2c6/32.png) [@scicco](https://forum.inductiveautomation.com/u/scicco)\
**Post date:** [February 23, 2011, 2:15pm UTC](https://forum.inductiveautomation.com/t/running-totals-sql-temp-tables/2676/1 "2011-02-23T14:15:59Z")

</div>

Hello,

I would like to chart running totals. I have a stored procedure (SQL Server 2005) that returns this in a temp table but the Data property of the Chart doesn’t seem to like it coming from a temp table. Error Message:

Exception: Error running query:  
SQLQuery(query=EXEC usp\_Overview\_Hourly\_Shift\_Data\_Chart  
5,  
22,  
2,  
2011,  
8,  
1,  
22,  
1062.5, database=)@5000ms  
On: Window.Root Container.Chart.Data  
caused by GatewayException: Gateway Error 500: The statement did not return a result set.  
caused by SQLServerException: The statement did not return a result set.

So I have a couple questions:

- Is it true that I can’t use a temp table?
- If so, is there any kind of functionality in your charts to tabulate running totals?

My temp table results are as follows:

Date\_Time\_Stamp RunningQuantity RunningTarget  
2011-02-22 05:00:00 7423 8500.00  
2011-02-22 04:00:00 6964 7437.50  
2011-02-22 03:00:00 6223 6375.00  
2011-02-22 02:00:00 5158 5312.50  
2011-02-22 01:00:00 4216 4250.00  
2011-02-22 00:00:00 3075 3187.50  
2011-02-21 23:00:00 2452 2125.00  
2011-02-21 22:00:00 1617 1062.50

And they come from these results from a select statement that is then inserted into my temp table and totaled:

Date\_Time\_Stamp Quantity Target  
2011-02-22 05:00:00 459 1062.50  
2011-02-22 04:00:00 741 1062.50  
2011-02-22 03:00:00 1065 1062.50  
2011-02-22 02:00:00 942 1062.50  
2011-02-22 01:00:00 1141 1062.50  
2011-02-22 00:00:00 623 1062.50  
2011-02-21 23:00:00 835 1062.50  
2011-02-21 22:00:00 1617 1062.50

Thanks!  
Steve

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [February 23, 2011, 4:31pm UTC](https://forum.inductiveautomation.com/t/running-totals-sql-temp-tables/2676/2 "2011-02-23T16:31:30Z")

</div>

You should be able to use a temp table. Does your stored procedure do a select query as the result or does it set an output parameter? Can you post an example?

---

<div class="post-metadata">

**Author:** ![scicco](https://avatars.discourse-cdn.com/v4/letter/s/e0b2c6/32.png) [@scicco](https://forum.inductiveautomation.com/u/scicco)\
**Post date:** [February 23, 2011, 4:51pm UTC](https://forum.inductiveautomation.com/t/running-totals-sql-temp-tables/2676/3 "2011-02-23T16:51:44Z")

</div>

Hi Travis,

It’s a select query from the temp table:

[code]ALTER PROCEDURE [dbo].[usp\_Overview\_Hourly\_Shift\_Data\_Chart\_TEMP]

@Hour varchar(2),  
@Day varchar(2),  
@Month varchar(2),  
@Year varchar(4),  
@Machine\_ID tinyint,  
@BU\_ID tinyint,  
@Shift\_Start tinyint,  
@Hour\_Target numeric(8,2)

AS

DECLARE @Date varchar(16)  
SET @Date = @Month + ‘/’ + @Day + ‘/’ + @Year + ’ ’ + @Hour + ‘:00’

DECLARE @Date\_Current\_Hour smalldatetime  
SET @Date\_Current\_Hour = CONVERT(smalldatetime, @Date)

DECLARE @Hours\_Back smallint

IF (@Shift\_Start = 22 AND @Hour \< 22)  
BEGIN  
SET @Hours\_Back = (@Shift\_Start - 24) - CONVERT(smallint, @Hour) --Needed since third shift is across two days  
END  
ELSE  
BEGIN  
SET @Hours\_Back = @Shift\_Start - CONVERT(smallint, @Hour)  
END

CREATE TABLE #ShiftByHour(  
Date\_Time\_Stamp smalldatetime,  
Quantity int,  
Target numeric(8,2),  
RunningQuantity int,  
RunningTarget numeric(8,2)  
)

INSERT INTO #ShiftByHour

SELECT  
Date\_Time\_Stamp,  
Quantity ‘Actual’,  
@Hour\_Target ‘Target’,  
0,  
0  
FROM  
Throughput\_Hourly  
WHERE  
Machine\_ID = @Machine\_ID AND  
BU\_ID = @BU\_ID AND  
Date\_Time\_Stamp \>= DATEADD(hh, @Hours\_Back, @Date\_Current\_Hour) AND  
Date\_Time\_Stamp \<= @Date\_Current\_Hour  
ORDER BY  
Date\_Time\_Stamp

DECLARE @RunningQuantity int  
DECLARE @RunningTarget numeric(8,2)

SET @RunningQuantity = 0  
SET @RunningTarget = 0

UPDATE  
#ShiftByHour  
SET  
@RunningQuantity = RunningQuantity = @RunningQuantity + Quantity,  
@RunningTarget = RunningTarget = @RunningTarget + Target

SELECT  
Date\_Time\_Stamp,  
RunningQuantity,  
RunningTarget  
FROM  
#ShiftByHour [/code]

I did find a way around it by not using temp tables but would still prefer that route if I can.

Thanks again,  
Steve

---

<div class="post-metadata">

**Author:** ![Travis.Cox](https://avatars.discourse-cdn.com/v4/letter/t/b9bd4f/32.png) [@Travis.Cox](https://forum.inductiveautomation.com/u/Travis.Cox)\
**Post date:** [February 23, 2011, 5:18pm UTC](https://forum.inductiveautomation.com/t/running-totals-sql-temp-tables/2676/4 "2011-02-23T17:18:07Z")

</div>

I think the issue is that you net to set nocount on in the stored procedure. With it off it returns number of rows rather than the data. You just need to run`SET NOCOUNT ON;`as the first line of the procedure. Here is my example proc:[code]ALTER PROCEDURE [dbo].[sp\_selectoutput]  
@input1 int  
AS  
BEGIN  
– SET NOCOUNT ON added to prevent extra result sets from  
– interfering with SELECT statements.  
SET NOCOUNT ON;

```
CREATE TABLE #RecipeTest(
	Name varchar(255),
	SP1 float,
	SP2 float,
	SP3 float
	);
	
INSERT INTO #RecipeTest SELECT Name, SP1, SP2, SP3 FROM Recipes WHERE SP2 < @input1;

SELECT Name, SP1, SP2, SP3 FROM #RecipeTest;

```

END[/code]The table’s data is bound to a SQL query:`EXEC sp_selectoutput 6`I get results back. Let me know what you find.

---

<div class="post-metadata">

**Author:** ![scicco](https://avatars.discourse-cdn.com/v4/letter/s/e0b2c6/32.png) [@scicco](https://forum.inductiveautomation.com/u/scicco)\
**Post date:** [February 23, 2011, 6:08pm UTC](https://forum.inductiveautomation.com/t/running-totals-sql-temp-tables/2676/5 "2011-02-23T18:08:10Z")

</div>

Works perfectly - thank you!!!