Peak Sum of values based on timestamp

[kfbeal]

We have an SQL database that I connect to via MS Access for a variety of queries. We have multiple customer accounts that we totalize their peak KW from multiple electric meters. For instance we bill them based on their total peak KW for the highest 15 minute interval between a certain date/time each month. It would be the highest sum value for any 15 minute interval of all the meters in question.

For instance, we would totalize each of the meters 15 minute interval between 05/01/2021 & 05/31/2021 and the largest of those intervals would be the billing KW.

So we would need to sum up a multitude of meters and grab the highest KW based on that calculation & 15 minute interval. I'm not sure if this is confusing or help in understanding.



I posted about something similar a while back, but things have changed since then. Since the data is already in an existing database, I didn't think pasting into Excel or MS Access would be worthwhile? I was hoping to use MS Access or a Pivot Table to accomplish what I'm needing to do, but drawing a blank on how to do so.

	Meter 1	Meter 2	Meter 3	Meter 4	Peak KW
6/7/2021 2:05	45.7	854.3	384.2	238.5	1522.7

Would using a Pivot Table be an easier route or some sort of access query/report?

It's a matter of putting all of the date/time and aligning the values up with the appropriate interval, sum'ing each interval and taking the largest of them all.

[kfbeal]

I should also mention this will be done for multiple accounts, so we'll have several accounts that we have to grab their total KW based on the largest interval.

[Micron]

If that is the way your source data is then IMO the first step is to normalize it. Since you would not be able to do this at the source, you'd have to link to it (as I presume you're already doing) then normalize it via UNION query into a second source table. However, seeing that you only provided one record it's impossible to be certain. If you ever had to add another meter and that forced you to add another field, then definitely it is in the wrong layout. I'm thinking more along the lines of (don't worry if I didn't match the meter value and meter number)

ReadDateID	ReadDate	Meter	MeterRead
1	6/7/2021 2:05	4	238.5
2	6/7/2021 2:05	2	384.2
3	6/7/2021 2:05	1	45.7
4	6/7/2021 2:05	3	854.3

Your example layout is like a spreadsheet, which doesn't work well in Access.

[kfbeal]

That was just filler data I put into a spreadsheet as an example of the values & totalization for an interval, that may have been a poor example I apologize. The values are stored as you mentioned:

DEVICENAME TIMESTAMP VALUE METERNUMBER
4302030303 3/2/2021 10:30:00 AM 333.2 5979
4302003333 3/2/2021 10:30:00 AM 488.2 6100
4302030333 3/2/2021 10:30:00 AM 288.3 5900
4020303033 3/2/2021 10:30:00 AM 383.4 6503

It would need to look at each 15 minutes interval and SUM a selected group of meters and display the largest of those SUM's. I hope I'm not confusing more than explaining.

[kfbeal]

I could also export the values with timestamp out into an Excel Spreadsheet for each meter, then put them all together in a single spreadsheet. But I would like to automate the process as much as possible. Thanks for your reply earlier.

[ssanfu]

DEVICENAME	TIMESTAMP	READING	METERNUMBER
4302030303	3/2/2021 10:30	333.2	5979
4302003333	3/2/2021 10:30	488.2	6100
4302030333	3/2/2021 10:30	288.3	5900
4020303033	3/2/2021 10:30	383.4	6503

We have an SQL database that I connect to via MS Access for a variety of queries. We have multiple customer accounts that we totalize their peak KW from multiple electric meters. For instance we bill them based on their total peak KW for the highest 15 minute interval between a certain date/time each month. It would be the highest sum value for any 15 minute interval of all the meters in question.

For instance, we would totalize each of the meters 15 minute interval between 05/01/2021 & 05/31/2021 and the largest of those intervals would be the billing KW.

So we would need to sum up a multitude of meters and grab the highest KW based on that calculation & 15 minute interval. I'm not sure if this is confusing or help in understanding.

For me, it would be very helpful to see multiple (5 - 10) 15 minute intervals for multiple (3 -4) meters and the results (Peak KW) you expect...

[Micron]

Better but still not clear for me. I think you mean group by meter over 15 minute intervals, not a group over 15 minute intervals with 15 minute sums per meter. In the first case there'd be several meters in an interval. In the second, there'd be one meter with a group of readings over 15 minutes.

look at each 15 minutes interval and SUM a selected group of meters and display the largest of those SUM's.

Regardless, I don't see how you can get the largest sum over a group if the group has 4 separate meters in it. You could get the sum or the min or max values. Unless that means the largest group sum over all groups.

Again, more data would have helped. You know exactly what you want but if you can't describe it 100% clearly, posting enough data to show the inputs and a few results would go a long way, and is likely faster anyway. For that I'd populate a spreadsheet that looks like the data and include some desired results. It drops in here as a decently formatted table.

If there is a query solution it will likely be outside my expertise but we do have query wizards here. However, it might require a code solution because there's one other aspect yet to be revealed - whether or not the intervals are recorded in 15 minute chunks or are a moving time span, e.g.

10:15
10:30
10:45 (2 15 minute intervals, each one contiguous) or

10:15
10:20
10:25
10:30
10:35 (2 15 minute intervals, non-contiguous)

Hopefully all that illustrates that the concept is never as clear or simple to anyone helping as it is to you.

[CJ_London]

I was going to ask - a 15 minute interval starts from when?

From time of first reading for the day/week/month/start of contract?
From midnight or some other daily start point?
You look at each reading and find any further readings within the next 15 minutes?
Is the interval start the same for all clients

Your example data shows hour and minute so presumably seconds are ignored and the rounding to full minutes is accepted as is

[Welshgasman]

Is this in the UK?

[kfbeal]

Column A-B is Meter 1 data, Row C-D is Meter 2 data, and Column E-F is Meter 3 data. I simply exported 3 different meters and put them side by side in Excel. As you can see, not all of the intervals will match up due to communications, lost values, etc. so we need to align those.

The lower half below the grey divider I removed the KW from the value fields and manually aligned the values based on date/time.

Column A-B are what are exported from the database for meter 1, I removed the additional 1,000's of rows for each meter.

We are totalizing each 15 minute interval for 4 different meters to make one value.archived_data (2).zip

Does this help?

[Micron]

There's no indication of what the calculations/results are that you are trying to achieve? I don't see how that answers any of the multiple questions that have been asked, sometimes more than once, so I'm going to unsubscribe. Good luck.

[Gicu]

Is this what you're after?

Cheers,

[CarlettoFed]

It would be advisable that you attach the Access file you created to actually see how the data are stored within it, also you should say if you enter the consumption data manually or are imported with a procedure from another Excel or Text file.
In Gicu's example some data regarding the measurements are missing, but the procedure to follow is the one or the grouping with the calculation of the Total consumption.

[CJ_London]

As some responder once said - if you can tell us the colour of your car, that might help us to understand better.

I’m sure this question has been asked before but cannot remember how long ago or which forum.

[kfbeal]

I am using an access database to connect to an SQL database. I exported the access query I've been using to grab some data, which is for meter numbers 5233 & 5236. What I am needing is to have the KW side by side for all of the meters I query for each timestamp interval. Some accounts may have 6 meters that I need to totalize the intervals. Essentially I am trying to align all the meter KW values based on the date timestamp and use Excel or possibly Access to sum up the KW of all meters for each interval so I can determine what the largest value of those sums are and use that for billing. I may not be easily explaining this, but thanks for the help.

The screenshot is what I'm trying to accomplish in the end, a timestamp with a KW value for each meter left to right so I can totalize them and then find the peak of that totalization. The 5236 & 5233 are the meters numbers that the values below them belong to. The sum is manually calculated in Excel. If that can be done via a report of some sort that would be great also.