"News" <ws*********@worldnet.att.net> wrote in message
news:8s*********************@bgtnsc04-news.ops.worldnet.att.net...
Folks,
I need help with this task. I have a set of data that needs to be plotted on
timeline chart.
Example:
Unit ProcStart ProcEnd Machine
U1 5/5/03 6:01 5/5/03 6:04 M1
U2 5/5/03 6:03 5/5/03 6:05 M1
U3 5/5/03 6:03 5/5/03 6:04 M2
:
etc. There are about 40K units and 30 serving machines. The data is in
MSAccess / SQLServer. My overall task is to plot concurrent processing at
each machine at each 1-minute interval.
Example for M1:
count
3|
2| * *
1| * * *
|______________________________
6:01 6:02 6:03 6:04 6:05 6:06
My thought is that if I can break down each row of data into smaller
timechunk, then I can do grouping and counts based on the new data -- which
is something like:
Unit ProcTime Machine
U1 6:01 M1
U1 6:02 M1
U1 6:03 M1
U1 6:04 M1
U2 6:03 M1
U2 6:04 M1
U2 6:05 M1
U3 6:03 M2
U3 6:04 M2
Then I was thinking to do query with: Machine, ProcTime, Count(Unit). Then I
can export the data into Excel and plot it.
My questions:
1) How to break the original data into smaller chunk timeframe (i.e. query,
stored procedure, etc.)?
2) Is this a sound approach? Any suggestions for better idea to achieve the
task?
Cheers,
-Jon
CREATE TABLE Processes
(
unit VARCHAR(10) NOT NULL,
proc_start SMALLDATETIME NOT NULL,
proc_end SMALLDATETIME NOT NULL,
machine VARCHAR(10) NOT NULL,
CONSTRAINT time_ck CHECK (proc_start <= proc_end),
PRIMARY KEY (machine, proc_start, proc_end, unit)
)
-- Sample data
INSERT INTO Processes (unit, proc_start, proc_end, machine)
SELECT 'U1', '20030505 6:01', '20030505 6:04', 'M1'
UNION ALL
SELECT 'U2', '20030505 6:03', '20030505 6:05', 'M1'
UNION ALL
SELECT 'U3', '20030505 6:03', '20030506 6:04', 'M2'
CREATE TABLE Digits
(
d TINYINT NOT NULL PRIMARY KEY CHECK (d BETWEEN 0 AND 9)
)
INSERT INTO Digits (d)
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
CREATE TABLE NonnegativeIntegers
(
n INT NOT NULL PRIMARY KEY CHECK (n >= 0)
)
INSERT INTO NonnegativeIntegers (n)
SELECT Ones.d + 10 * Tens.d + 100 * Hundreds.d +
1000 * Thousands.d
FROM Digits AS Ones
CROSS JOIN
Digits AS Tens
CROSS JOIN
Digits AS Hundreds
CROSS JOIN
Digits AS Thousands
SELECT P.machine AS machine,
DATEADD(MINUTE, Minutes.m, Days.d) AS time,
COUNT(*) AS process_total
FROM (SELECT n
FROM NonnegativeIntegers
WHERE n < 1440) AS Minutes(m) -- minutes in 1 day
CROSS JOIN
(SELECT Interval.first_date + I.n
FROM (SELECT CAST(CONVERT(CHAR(8), MIN(proc_start), 112)
AS SMALLDATETIME) AS first_date,
DATEDIFF(DAY, MIN(proc_start),
MAX(proc_end)) AS days
FROM Processes) AS Interval
INNER JOIN
NonnegativeIntegers AS I
ON I.n <= Interval.days) AS Days(d)
INNER JOIN
Processes AS P
ON DATEADD(MINUTE, Minutes.m, Days.d) BETWEEN
P.proc_start AND P.proc_end
GROUP BY P.machine, Days.d, Minutes.m
ORDER BY P.machine, Days.d, Minutes.m
machine time process_total
m1 2003-05-05 06:01:00 1
m1 2003-05-05 06:02:00 1
m1 2003-05-05 06:03:00 2
m1 2003-05-05 06:04:00 2
m1 2003-05-05 06:05:00 1
m2 2003-05-05 06:03:00 1
m2 2003-05-05 06:04:00 1
Regards,
jag
