Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Grouping by day on a rollover count Expand / Collapse
Author
Message
Posted Friday, June 14, 2013 2:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
Hi,
I have a table containing a count that rollsover at around 32000 to 0 possibly many times a day and I want to extract the total count for each day for x days. I can do it for the overall report length by the code below but I cannot GROUP BY dateadd(DAY,0, datediff(day,0, t_stamp)) because of the TOP command not being an aggregate. I seem to build 'chunks' of working code and then try to stitch them together cause I'm a NooB. Has any one got any ideas of how I should approach this?


Thanks

SELECT
(SELECT 32000-(SELECT top 1 open_front
FROM AP7_E_Open_Flap_Test
WHERE t_stamp > '2013-06-13 00:00:00' and t_stamp < '2013-06-13 23:59:59'
and open_front IS NOT NULL
ORDER BY t_stamp))
+
(SELECT TOP 1 open_front
FROM AP7_E_Open_Flap_Test
WHERE t_stamp > '2013-06-13 00:00:00' and t_stamp < '2013-06-13 23:59:59'
and open_front IS NOT NULL
ORDER BY t_stamp DESC)
+
(SELECT (COUNT(*)-1)*32000
FROM AP7_E_Open_Flap_Test t1
FULL OUTER JOIN AP7_E_Open_Flap_Test t2
ON t1.AP7_E_Open_Flap_Test_ndx = t2.AP7_E_Open_Flap_Test_ndx -1
WHERE t2.open_front < t1.open_front
AND t1.t_stamp > '2013-06-13 00:00:00' and t1.t_stamp < '2013-06-13 23:59:59')

I found this code on the net to replace TOP in a GROUP
SELECT wrap_count -- end of day
,dateadd(DAY,0, datediff(day,0,t_stamp)) AS [Date]
FROM (SELECT
ROW_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t_stamp)) ORDER BY t_stamp DESC ) AS 'RowNumber',
wrap_count,
t_stamp
FROM AP7_E_Open_Flap_Test
) dt
WHERE RowNumber =1
ORDER BY dateadd(DAY,0, datediff(day,0,t_stamp))

but again I'm trying to add/join (not sure which at this stage) lots of 'bits'.
Post #1463419
Posted Friday, June 14, 2013 3:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
I cant believe it but I MAY have found a solution:
SELECT ISNULL(32000.0-A.wrap_count+B.wrap_count+C.[Rollover_Count],0) AS [Count], A.Date 
FROM
(SELECT
ROW_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t_stamp)) ORDER BY t_stamp) AS 'RowNumber',
wrap_count,
dateadd(DAY,0, datediff(day,0, t_stamp)) AS [Date]
FROM AP7_E_Open_Flap_Test
WHERE t_stamp > '2013-06-13 00:00:00' and t_stamp < '2013-06-13 11:59:00')A -- put report dates in here
LEFT OUTER JOIN
(SELECT
ROW_NUMBER() OVER ( PARTITION BY dateadd(DAY,0, datediff(day,0,t_stamp)) ORDER BY t_stamp DESC ) AS 'RowNumber',
wrap_count,
dateadd(DAY,0, datediff(day,0, t_stamp))AS [Date]
FROM AP7_E_Open_Flap_Test
WHERE t_stamp > '2013-06-13 00:00:00' and t_stamp < '2013-06-13 11:59:00')B
ON A.Date = B.Date
LEFT OUTER JOIN
(SELECT (COUNT(*)-1)*32000.0 as [Rollover_Count]
,dateadd(DAY,0, datediff(day,0, t1.t_stamp)) AS [Date]
FROM AP7_E_Open_Flap_Test t1
FULL OUTER JOIN AP7_E_Open_Flap_Test t2
ON t1.AP7_E_Open_Flap_Test_ndx = t2.AP7_E_Open_Flap_Test_ndx -1
WHERE t2.wrap_count < t1.wrap_count AND t2.wrap_count IS NOT NULL
AND t1.t_stamp > '2013-06-13 00:00:00' and t1.t_stamp < '2013-06-13 11:59:00' -- 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

Any comments welcome (please) cause I mackled this together so I presume there is a better method. Need a lie down now.

P.S. Edited a couple of times as little bugs pop up like the ISNULL on the initial SELECT line for when the count remains static for that day (no change in count values)
Post #1463445
Posted Friday, June 14, 2013 4:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
OK, found HUGE stumbling block. The code below returns nothing if there is no rollover occuring in that day whereas I need it to return -32000 with that days date so it can be joined to the first two subqueries

SELECT (COUNT(*)-1)*32000 as [Rollover_Count]
,dateadd(DAY,0, datediff(day,0, t1.t_stamp)) AS [Date]
FROM AP7_E_Open_Flap_Test t1
FULL OUTER JOIN AP7_E_Open_Flap_Test t2
ON t1.AP7_E_Open_Flap_Test_ndx = t2.AP7_E_Open_Flap_Test_ndx -1
WHERE t2.open_front < t1.open_front AND t2.open_front IS NOT NULL
AND t1.t_stamp > '2013-06-06 00:00:00' and t1.t_stamp < '2013-06-06 11:59:00' -- Also need report dates here
group by dateadd(DAY,0, datediff(day,0, t1.t_stamp))

Bah - come all this way and now I'm at a dead end
Post #1463470
Posted Friday, June 14, 2013 5:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
Haha!!! Put a ISNULL in top line to change no rollover to -32000.
Going to give it a test.

SELECT ISNULL(32000.0-A.open_front + B.open_front+ISNULL(C.[Rollover_Count],-32000),0) AS [Count], A.Date 
FROM
(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 AP7_E_Open_Flap_Test
WHERE t_stamp > '2013-06-04 00:00:00' and t_stamp < '2013-06-06 01:59:00')A -- put report dates in here
LEFT OUTER JOIN
(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 AP7_E_Open_Flap_Test
WHERE t_stamp > '2013-06-04 00:00:00' and t_stamp < '2013-06-06 01:59:00')B
ON A.Date = B.Date
LEFT OUTER JOIN
(SELECT (COUNT(*)-1)*32000.0 as [Rollover_Count]
,dateadd(DAY,0, datediff(day,0, t1.t_stamp)) AS [Date]
FROM AP7_E_Open_Flap_Test t1
FULL OUTER JOIN AP7_E_Open_Flap_Test t2
ON t1.AP7_E_Open_Flap_Test_ndx = t2.AP7_E_Open_Flap_Test_ndx -1
WHERE t2.open_front < t1.open_front AND t2.open_front IS NOT NULL
AND t1.t_stamp > '2013-06-04 00:00:00' and t1.t_stamp < '2013-06-06 01:59:00' -- 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

Post #1463481
Posted Saturday, June 15, 2013 5:10 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
Hi Brett,

Seems you are slogging around and getting somewhere step by step. It would however be very useful if you were to post DDL for the tables involved, plus some test data as INSERT statements, as well as the results you are expecting from your query. That way we would be able to assist you. Just a vague statement about what you are after can only keep us guessing. Please follow the link in my signature on how to help us assisting you.

Regards,
Jan



--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1463913
Posted Monday, June 17, 2013 4:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
OK, read the Forum Etiquette and hope this is correct.

DECLARE @T TABLE
(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');

DECLARE @StartDate DATETIME
SET @StartDate = '2013-01-01 00:00'
DECLARE @EndDate DATETIME
SET @EndDate= '2013-01-02 23:59';

The results I would want to see back from the above @StartDate and @EndDate would be:

Open Front..........Open Rear........Wrap_Count........Date
2.......................100.................30000................1/1/2013
32006.................0....................39000................2/1/2013

(Rembering that the rollover is to be set at 32000 so any decrease in count indicates that 32000 was reached in the PLC)
Hope this is OK.
Thanks
Post #1464062
Posted Monday, June 17, 2013 7:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
Yes, that's a good effort. However, from that test data I can really not see how you would get to the desired results. Could you please explain the algorithm behind that?

Regards,
Jan


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1464132
Posted Monday, June 17, 2013 7:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
The PLC counts objects, when the PLC count reaches 32000 it resets to zero so in the PLC you see 1,2,3,4,5,........31999,32000,0. But the transaction group to SQL only runs every 60 seconds so you may see 31989,31995,2 in SQL hence why I cannot look for 32000 but have to detect when the count value is smaller than the previous.

Basically therefore I am looking for:
@32000 - the first value for the report
@The amount of rollovers -1 (as described above) * 32000
@The last value at the report end.

Added together you get the number of 'things' from start to end.

I hope this clears things up. (I tend to think of the data drawn like a 'saw tooth' shape with vertical drops (32000 ish down to zero) and I want to delve in and obtain the counts from start to end).

Post #1464150
Posted Monday, June 17, 2013 12:15 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 26, 2014 3:23 PM
Points: 2,116, Visits: 6,439
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


--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)

In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)
Post #1464301
Posted Wednesday, July 3, 2013 3:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:19 AM
Points: 42, Visits: 161
Jan,
Thanks, but this is not the result I am after. My table has three count columns (open_front, open_rear, wrap_count) plus a timestamp of when the record was added (t_stamp). Each count column is a counter value in a PLC (so 3 independant counters). The PLC counters resets to zero at 32000 (any counter can reset to zero independant of the other two counters - by the time a new row is INSERTED the counter value could already have incremented away from zero so the rows could go 31998, 2, 3, 8 etc) but there is no recording of this happening so it has to be 'detected' in the query by the previous row being higher than the row beneath it (i.e. 31999 followed by 1 = a rollover).
(see drawing attached - I know drawings are not the usual thing but I'm hoping it helps).

My results need to be a count (as described above and in drawing) for open_front, open_rear, wrap_count grouped by day:

Open Front..........Open Rear........Wrap_Count........Date
2.......................100.................30000................1/1/2013
32006.................0....................39000................2/1/2013

From the test data open_front for the 2/1/2013 resulted from:
Initial value :2 (therefore 32000 - 2) = 31998
Rollover count:1 (therefore 1-1*32000) = 0
End value = 8

Giving a total for the 2/1/2013 for open_front: 32006

For wrap_count on the 2/1/2013 we see this:
Initial value:31000 (therefore 32000 - 31000) = 1000
Rollover count:2 (therefore 2-1*32000) = 32000
End value:6000

Giving a total for 2/1/2013 for wrap_count: 39000

The test data might not look logical but I was trying to show that 0 and 32000 (top and bottom values) are not always recorded in the SQL due to a slow INSERT of data but a fast PLC count rate but a count had to have reached 32000 before reseting even if the SQL showed:

31970
31988
1
10

Thanks





  Post Attachments 
SQL.pdf (5 views, 89.03 KB)
Post #1469936
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse