cbabb,
If it helps I did something similar (ish) when tracking PLC counts. Since the PLC resets to zero when it reaches 32000 the graph looked the same as yours.
The first method was to join the table to itself on table1.ndx = table2.ndx and determine when the value in table1 is less than table2 and count the ‘rollover’. You could get the timestamps etc and do calculate your other data. My example is below, the result is from the first column of the temp table.
[code]DECLARE @T TABLE
(ndx INT IDENTITY ( 1, 1 ) NOT NULL,
open_front INT
,open_rear INT
,wrap_count INT
,t_stamp DATETIME);
INSERT INTO @T VALUES (0,0,0,‘2013-01-01 01:00’);
INSERT INTO @T VALUES (1,10,1000,‘2013-01-01 02:00’);
INSERT INTO @T VALUES (1,20,2000,‘2013-01-01 03:00’);
INSERT INTO @T VALUES (2,100,30000,‘2013-01-01 23:59’);
INSERT INTO @T VALUES (2,100,31000,‘2013-01-02 01:00’);
INSERT INTO @T VALUES (4,100,150,‘2013-01-02 01:30’);
INSERT INTO @T VALUES (500,100,4000,‘2013-01-02 03:00’);
INSERT INTO @T VALUES (4000,100,31000,‘2013-01-02 05:00’);
INSERT INTO @T VALUES (7000,100,8,‘2013-01-02 08:00’);
INSERT INTO @T VALUES (30000,100,4000,‘2013-01-02 10:00’);
INSERT INTO @T VALUES (8,100,6000,‘2013-01-02 21:00’);
–The SELECT below adds the three values obtained((32000 - initial) + last value + (rollovers-132000))
SELECT ISNULL(32000.0-A.open_front + B.open_front+ISNULL(C.[Rollover_Count],-32000),0) AS [Count], A.Date
FROM --Below is the initial value
(SELECT
ROW_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t_stamp)) ORDER BY t_stamp) AS ‘RowNumber’,
open_front,
dateadd(DAY,0, datediff(day,0, t_stamp)) AS [Date]
FROM @t
WHERE t_stamp > ‘2012-12-31 23:59:59’ and t_stamp < ‘2013-07-02 23:59:59’)A – put report dates in here
LEFT OUTER JOIN --Below is the end value
(SELECT
ROW_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t_stamp)) ORDER BY t_stamp DESC ) AS ‘RowNumber’,
open_front,
dateadd(DAY,0, datediff(day,0, t_stamp))AS [Date]
FROM @t
WHERE t_stamp > ‘2012-12-31 23:59:59’ and t_stamp < ‘2013-07-02 23:59:59’)B
ON A.Date = B.Date
LEFT OUTER JOIN --Below is the number of rollovers
(SELECT (COUNT()-1)*32000.0 as [Rollover_Count]
,dateadd(DAY,0, datediff(day,0, t1.t_stamp)) AS [Date]
FROM @t t1
FULL OUTER JOIN @t t2
ON t1.ndx = t2.ndx -1
WHERE t2.open_front < t1.open_front AND t2.open_front IS NOT NULL
AND t1.t_stamp > ‘2012-12-31 23:59:59’ and t1.t_stamp < ‘2013-07-02 23:59:59’ – Also need report dates here
group by dateadd(DAY,0, datediff(day,0, t1.t_stamp)))C
ON B.Date = C.Date
WHERE A.Rownumber = 1 AND B.Rownumber = 1
ORDER BY A.Date[/code]
I found this method very slow to execute when using a lot of data.
Personally I opted for transaction groups that run queries against the data and decide when a ‘rollover’ (for you a drain and could be logged in an additional column as ‘1’ along with timestamps etc) has occured.
In my transaction group as well as recording the current value I have this triggered expression:
SELECT CASE WHEN
ISNULL((SELECT TOP 1 Line1Count
FROM AP4_Packing_Counts
ORDER BY t_stamp DESC) ,0)
<= {[~]Burton Latimer/AP4/Palletiser/Line 1 Kappa Outfeed Count}
THEN
(SELECT {[~]Burton Latimer/AP4/Palletiser/Line 1 Kappa Outfeed Count} - ISNULL((SELECT TOP 1 Line1Count
FROM AP4_Packing_Counts
ORDER BY t_stamp DESC),0))
ELSE
(SELECT 32000 - ISNULL((SELECT TOP 1 Line1Count
FROM AP4_Packing_Counts
ORDER BY t_stamp DESC),0)+ {[~]Burton Latimer/AP4/Palletiser/Line 1 Kappa Outfeed Count} )
END
So every one minute (transaction group rate) I decide whether the value is higher than the last and if so record the difference, if it is lower than the last do something different (32000-last value + current). This method made the querying of SQL data way… easier when reporting on the values.
Probably not exactly what your after