Well, I'm still not sure whether I understood your requirements correctly, and guessing this is not an exam question (they don't usually get that comlicated) let's give it a bash, and please tell me if I'm going wrong somewhere. I'll show what I came up with a kind of Divide and Conquer method, rather than end up with a single query.
First I added an IDENTITY column plus some more columns to your test data, as shown here, see the comments as to why.
-- Same as @T, just like table names to be meaningful
DECLARE @measurement TABLE
(
-- Added an identity column to uniquely identify the row. Used later in an UPDATE.
measure_id INT IDENTITY ( 1, 1 ) NOT NULL,
-- The original columns
open_front INT NOT NULL,
open_rear INT NOT NULL,
wrap_count INT NOT NULL,
t_stamp DATETIME NOT NULL,
-- To keep the difference between the current and previous reading to be calculated later.
-- Note the default: in case there is no previous reading, assume it started at 0.
wrap_diff INT NOT NULL DEFAULT 0,
-- If wrap_diff becomes negative (once calculated), there was a rollover,
-- so adjust for that with a calculated column
wrap_diff_adj AS
CASE
WHEN wrap_diff < 0 THEN wrap_diff + 32000
ELSE wrap_diff
END,
-- Get rid of the time part of the t_stamp so we can group on it.
-- Basecally calculates the number of days between t_stamp and 1 January 1900,
-- Then adds those number of days back to 1 January 1900 to get to midnight on that day.
t_date AS
DATEADD ( day, 0, DATEDIFF ( day, 0, t_stamp ) )
);
Then insert your test data. Note that I have named the columns explicitly, which is always good practice, just in case columns are added or shifted around.
-- Insert the test data. Always be explicit about the columns you are inserting to
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (0,0,0,'2013-01-01 01:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (1,10,1000,'2013-01-01 02:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (1,20,2000,'2013-01-01 03:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (2,100,30000,'2013-01-01 23:59');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (2,100,31000,'2013-01-02 01:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (4,100,150,'2013-01-02 01:30');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (500,100,4000,'2013-01-02 03:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (4000,100,31000,'2013-01-02 05:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (7000,100,8,'2013-01-02 08:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (30000,100,4000,'2013-01-02 10:00');
INSERT INTO @measurement (open_front, open_rear, wrap_count, t_stamp ) VALUES (8,100,6000,'2013-01-02 21:00');
Now find the difference in between the readings between this one and the previous one, using a self-join to the previous measure_id. Of course that assumes that measurements were added in the correct t_stamp order
-- Calculate the difference between this reading and the previous one.
-- Remember the calculated column wrap_diff_adj takes care of the rollovers.
UPDATE M2
SET
M2.wrap_diff = M2.wrap_count - M1.wrap_count
FROM
@measurement M1
JOIN
@measurement M2
ON
M2.measure_id = M1.measure_id + 1
-- What have we got in @meaurement?
SELECT * FROM @measurement ORDER BY measure_id
That gives us the following results:
measure_id open_front open_rear wrap_count t_stamp wrap_diff wrap_diff_adj t_date
----------- ----------- ----------- ----------- ----------------------- ----------- ------------- -----------------------
1 0 0 0 2013-01-01 01:00:00.000 0 0 2013-01-01 00:00:00.000
2 1 10 1000 2013-01-01 02:00:00.000 1000 1000 2013-01-01 00:00:00.000
3 1 20 2000 2013-01-01 03:00:00.000 1000 1000 2013-01-01 00:00:00.000
4 2 100 30000 2013-01-01 23:59:00.000 28000 28000 2013-01-01 00:00:00.000
5 2 100 31000 2013-01-02 01:00:00.000 1000 1000 2013-01-02 00:00:00.000
6 4 100 150 2013-01-02 01:30:00.000 -30850 1150 2013-01-02 00:00:00.000
7 500 100 4000 2013-01-02 03:00:00.000 3850 3850 2013-01-02 00:00:00.000
8 4000 100 31000 2013-01-02 05:00:00.000 27000 27000 2013-01-02 00:00:00.000
9 7000 100 8 2013-01-02 08:00:00.000 -30992 1008 2013-01-02 00:00:00.000
10 30000 100 4000 2013-01-02 10:00:00.000 3992 3992 2013-01-02 00:00:00.000
11 8 100 6000 2013-01-02 21:00:00.000 2000 2000 2013-01-02 00:00:00.000
Now to get to the result I think you are after, all we need to do is to add up the number of rollovers per day, which can be done as such:
DECLARE @StartDate DATETIME
SET @StartDate = '2013-01-01 00:00'
DECLARE @EndDate DATETIME
SET @EndDate= '2013-01-02 23:59';
SELECT
SUM ( wrap_diff_adj ) AS 'wrap_count',
t_date
FROM
@measurement
WHERE
t_date BETWEEN @StartDate AND @EndDate
GROUP BY
t_date
ORDER BY
t_date
Which gives us the following result:
wrap_count t_date
----------- -----------------------
30000 2013-01-01 00:00:00.000
40000 2013-01-02 00:00:00.000
Now, that's off by 1000 wrap_counts as per your sample result. Did I go wrong somewhere? Or is this just a calculation error on your part?
What's also missing is how you derive the "Open Front" and "Open Rear" values in your sample results. I can see where the ones for 1 January are coming from (they are the final readings for 1 January), but where does Open Front = 32006 and Open Rear = 0 for 2 January come from?
Let me know if this helps so far, and what I'm missing.
Regards,
Jan