Cumulative data for chart

Archived Topics
1

Hi

I am having trouble generating a dataset for a cumulative chart I want to plot. I basically want to plot a chart which will display a running target versus actual target with cumulative data as the shift progresses.i.e target is 525 per hour so at 9am 525 versus actual will be plotted, 10am 1050 versus actual will be plotted. I have a table which stores the target and actual outs for each hour with shiftname and timestamp with a new record been inserted every hour. I have tried using sql to create a new column with the cumulative values but it will only work on the whole table which is not what I want. I want to be able to display this per shift or 8 hour period. I have created a table which will display the 8 hours filtered by date and shiftname. What I am wondering is if it is possible to use scripting to work on this table and generate the cumulative values that I need. I see there is a toPyDataSet in the help menu that might work. I would appreciate any help on this.

Thanks

Aidan

2

I think your best solution will be to use a DB query to do your running totals.

Below are a couple of SQL queries that might work for you with simple alterations.

FYI, I’ve also included the code that I used to create my test table and data. I developed this on MS SQL Server 2005. The first query should work for both MS SQL Server and MySQL. The second uses a Common Table Expression (i.e., CTE) which works only in MS SQL Server.

Code to create test table:

USE [DatabaseName] GO /****** Object: Table [dbo].[Production] Script Date: 02/10/2010 20:35:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Production]( [day] [datetime] NOT NULL, [shift] [tinyint] NOT NULL, [hour] [tinyint] NOT NULL, [target] [int] NOT NULL, [actual] [int] NOT NULL, CONSTRAINT [PK_Production] PRIMARY KEY CLUSTERED ( [day] ASC, [shift] ASC, [hour] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
Code to populate table with test data:

[code]DECLARE @Date datetime
DECLARE @Shift tinyint
DECLARE @Hour tinyint

SET @Date = ‘20100201’
WHILE @Date < ‘20100210’ BEGIN
SET @Shift = 1
WHILE @Shift <= 3 BEGIN
SET @Hour = 0
WHILE @Hour <= 7 BEGIN
INSERT INTO Production
SELECT @Date, @Shift, @Hour, 525, 525 + rand() * 10 - 10
SET @Hour = @Hour + 1
END
SET @Shift = @Shift + 1
END
SET @Date = dateadd(day, 1, @Date)
END

SELECT *
FROM Production
[/code]
Code to query table for running totals:

[code]-- MS SQL Server & MySQL running total query
SELECT p.day,
p.shift,
p.hour,
(SELECT sum(t.target)
FROM Production t
WHERE t.day = p.day AND t.shift = p.shift AND t.hour <= p.hour
) AS target,
(SELECT sum(a.actual)
FROM Production a
WHERE a.day = p.day AND a.shift = p.shift AND a.hour <= p.hour
) AS actual
FROM Production p

– MS SQL Server running total query using CTE
; – Need semicolon before WITH statement

WITH ProductionCTE(day, shift, hour, target, actual)
AS
(
SELECT day,
shift,
hour,
target,
actual
FROM Production
)

SELECT day,
shift,
hour,
(SELECT SUM(target) FROM ProductionCTE cte1 WHERE cte1.hour <= cte3.hour AND cte1.day = cte3.day AND cte1.shift = cte3.shift) AS target,
(SELECT SUM(actual) FROM ProductionCTE cte2 WHERE cte2.hour <= cte3.hour AND cte2.day = cte3.day AND cte2.shift = cte3.shift) AS actual
FROM ProductionCTE cte3
[/code]

3

Hi MickeyBob

Thanks for the help. with a few adjustments for my table I got it to work

Aidan