June 14, 2013 at 2:20 am
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:crying:) lots of 'bits'.
June 14, 2013 at 3:16 am
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)
June 14, 2013 at 4:37 am
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 🙁
June 14, 2013 at 5:11 am
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
June 15, 2013 at 5:10 pm
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
June 17, 2013 at 4:24 am
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
June 17, 2013 at 7:14 am
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
June 17, 2013 at 7:50 am
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).
June 17, 2013 at 12:15 pm
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
July 3, 2013 at 3:25 am
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
July 5, 2013 at 1:18 am
This is my code for one column but I cannot work out how to get counts of multiple columns for the 'rollover' count.
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-1*32000))
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
July 8, 2013 at 7:35 am
Sorted it. Jans solution was correct. The difference in our results comes from the query using the differnece between the last value from the previous day and the first value for the grouped day in the total for the day being grouped on whereas my calculations on paper were done with just the values with the chosen date in the t_stamp regardless of the value from the day before!!!
Thanks Jan and sorry for the mix up.
July 15, 2013 at 5:28 am
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply