Break down cumulative data 1

[cterelley]

I have a table that reports errors on a router as of a particular time. This reports once a day just before midnight. The problem is, the router reports the cumulative errors since the last time it was rebooted (which clears the errors and starts the error counter at zero). Here is a sample of the data I'm receiving from one of the routers:


Date CRC Errors
10/18/2004 51
10/19/2004 65
10/20/2004 78
10/21/2004 90


Here's how I would like the data to display in a query so I can generate some graphs off of it:

Date CRC Errors
10/18/2004 51
10/19/2004 14
10/20/2004 13
10/21/2004 12

There are 19 routers reporting to this table once a day so I'm trying to do this for many devices. If there is someone who can help, it would be greatly appreciated.

Thanks

 

[mordja]

cterelley,

Assuming that you have data in ascending order and for each day you could do.


SELECT R.date, (R.crcerrors-S.crcerrors) AS Expr1
FROM test AS R, test AS S
WHERE (((R.date)=.[date]+1));


This will give you the difference between each row, but as there 4 rows you will get three differences(three rows) and the output would be

10/19/2004 14
10/20/2004 13
10/21/2004 12

Mordja

 

[mordja]

ctellery,

I had a think about it and revised the above to:


SELECT R.date, IIf(DMin("[date]","test")=[r].[date],[R].[crcerrors],([R].[crcerrors]-.[crcerrors])) AS Expr1
FROM test AS R, test AS S
WHERE (((R.date)=IIf(DMin("[date]","test")=[r].[date],.[date],.[date]+1)));


This now checks if the record is the first, if it is then join as normal and use the first crcerrors value. If not self join on date + 1. Obviously ive used a table called test with two fields date and crcerrors.

The result is now

Date CRC Errors
10/18/2004 51
10/19/2004 14
10/20/2004 13
10/21/2004 12


Mordja

 

[cterelley]

I've tried to incorporate this into my db but ran into some issues. All the data is stored in a table called RouterTable with the two fields DDateTime and CRC. When I replaced "test" with "RouterTable" and [date] with [DDateTime] and ran the query, it returned 361 repetitive records when it should have only returned 72. I'm probably doing this completely incorrect though.

 

[cterelley]

Here's a copy of my sql statement:

SELECT R.DDateTime, IIf(DMin("[DDateTime]","RouterTable")=r.DDateTime,R.crc,(R.crc-S.crc)) AS Expr1
FROM RouterTable AS R, RouterTable AS S
WHERE ((([R].[DDateTime])=IIf(DMin("[DDateTime]","RouterTable")=[r].[DDateTime],.[DDateTime],.[DDateTime]+1)));

 

[PHV]

And what about this ?
SELECT R.DDateTime, R.CRC-Nz(T.CRC) As CRC
FROM RouterTable AS R LEFT JOIN RouterTable AS T ON R.DDateTime = T.DDateTime+1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cterelley]

That string displays the data just as if I did a select of the two fields in that table. I've tried tweaking it a bit to no avail.

 

[PHV]

Works for me.
Try this to better understand whats happens:
SELECT R.DDateTime, (R.CRC-Nz(T.CRC,0)) As CRCerrors,R.CRC,T.CRC,T.DDateTime
FROM RouterTable AS R LEFT JOIN RouterTable AS T ON R.DDateTime = T.DDateTime+1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Oops, you said DateTime ?
Try this:
SELECT R.DDateTime, R.CRC-Nz(T.CRC) As CRCerrors
FROM RouterTable AS R LEFT JOIN RouterTable AS T
ON Int(R.DDateTime) = Int(T.DDateTime+1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cterelley]

Just as a test, I created a table with the entries from just one of the routers. When I ran this query:

SELECT R.DDateTime, (R.CRC-Nz(T.CRC,0)) As CRCerrors,R.CRC,T.CRC,T.DDateTime
FROM RouterTable AS R LEFT JOIN RouterTable AS T ON R.DDateTime = T.DDateTime+1;

Against that test table it worked great.

I think the problem I'm having is there are multiple devices reporting to the same table (For instance, on 10/18/04 there are 19 entries. And on 10/19/04 there are 19 entries and so on...). Each devices ID is 1 through 19. I probably should have said this earlier. Just as a sample, here is a portion of what the original table would look like.

Device Date CRC Errors
1 10/18/2004 51
2 10/18/2004 22
3 10/18/2004 37
1 10/19/2004 65
2 10/19/2004 38
3 10/19/2004 44
1 10/20/2004 78
2 10/20/2004 70
3 10/20/2004 49
1 10/21/2004 90
2 10/21/2004 98
3 10/21/2004 66


I would like it appear like this in a query:

Device Date CRC Errors
1 10/18/2004 51
2 10/18/2004 22
3 10/18/2004 37
1 10/19/2004 14
2 10/19/2004 16
3 10/19/2004 7
1 10/20/2004 13
2 10/20/2004 32
3 10/20/2004 5
1 10/21/2004 12
2 10/21/2004 28
3 10/21/2004 17

Sorry for not being more specific. Hopefully this helps!

Thanks

 

[PHV]

So, try this:
SELECT R.Device, R.DDateTime, (R.CRC-Nz(T.CRC,0)) As CRCerrors
FROM RouterTable AS R LEFT JOIN RouterTable AS T
ON (R.Device=T.Device) And (R.DDateTime=T.DDateTime+1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cterelley]

I copied and pasted that sql statement into my project. I produced the cumulative results instead of the individual errors for the day.

 

[PHV]

This works for me:
SELECT R.Device, R.DDateTime, (R.CRC-Nz(T.CRC,0)) As CRCerrors
FROM RouterTable AS R LEFT JOIN RouterTable AS T
ON R.Device=T.Device And Int(R.DDateTime)=Int(T.DDateTime+1)
ORDER BY 2,1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cterelley]

Thank you so much. I copied and pasted that into my project and it worked GREAT!!!! I don't know how I could ever repay you but you were awesome!