Running Totals / SQL Temp Tables

Ignition
1

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

2

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?

3

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

4

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 runSET 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 6I get results back. Let me know what you find.

1 Like
5

Works perfectly - thank you!!!