Efficient way to return the average based on condition and day/time

[HectorH]

Hello all,



I got a problem that I have a solution to but it is inefficient and I believe there is an easier way but it alludes me. Here it is:

I have two tables, one has equipment information i.e. size, the other contains hourly reads for each piece of equipment for a month duration. so 744 reads per equipment.

I want to get the day of the week averages for each piece of equipment that meets the size criteria. so For mondays I want the avr result from a "Large" equipment for Mon, tues Wed Thurs, friday.

What I did was create a query that brings in the size field so I just have one table than I use the davg function for each day. Code is very short but runtime is long.

Any suggestions

[rpeare]

D functions (dsum, davg, dmin, etc) are very inefficient especially over larger data sets.

as I understand it you want to see the average of all the mondays in a given month, an average for all the tuesdays in a given month, etc

What I would do in this case is use the weekday([DateField]) function. It will return a numeric value for the day of the week. Sunday Being 1, Saturday being 7. You can alter this by expressly telling the weekday which day to treat as 1 by inserting the number of the day you want to start in, for instance if you want to return a 1 for monday you would have

Weekday([DateField], 2)

Then you can average all of you data on a particular work day without using the davg function.

[HectorH]

I am actually using that within the dAVG. I just don't know how to get the avg of each day with the SIZE criteria from the other table being met. I can do it for one day but when I do it for multiple days it bugs out. I thought about using some sort of variable representing each column/day variables and using seven different subqueries but had no luck. After this I want to do the same query but with time of day. Get the 1pm average thru out the month.

Should look like this:

Monday AVG ,Tues AVG,....
10 15

[rpeare]

I don't know what your original dataset looks like but if your dates are in a normalized table what you're looking at with your desired output is a crosstab query.

If you can post an example of your starting data and what you want the output to be it would be easier to point you in the right direction.

[HectorH]

Crosstab, I've never used that before, I'll read up on it. I won't be working on that project for the next few days but I appreciate your input. At the very least I will have something to look into so thank you.

[HectorH]

So Im still stuck!

Here is the data set table

Read, Day stamp , Time Stamp, Size
10, 5, 5/10/11, 0:00, Large (example row)
This is how the data is laid out in a table.

I want the average reads by size for Mondays,....Sunday
I want the size, avg for mon, avg for tues, ...as columns
One row will show the size, and averages for each day of the week

Here is sQl to get just one size, I have various sizes
select a.[SIZE],avg(Read) AS Monday
from [Reads by Size] a where Weekday (Day, 2 )=1
group by a.[SIZE]
Now I need to add Tuesday average colum for each size and so on.

[rpeare]

You have four column (read, day stamp, time stamp, size) headings but five columns of data if you are separating by commas.

What does the column READ represent, the number of times something is measured or is it a primary key or what?

Let's say you had this data set (for this example I'm assuming the second column of your 'data' is just the day of the month and I am ignoring it), I am ignoring the time stamp because it's irrelevant to what you've asked as well.

Read DayStamp Size
10   5/10/11  Large
10   5/11/11  Large
11   5/9/11   Large
11   5/10/11  Large

What do you want the output to be?

Read Size   Monday Tuesday Wednesday ...
10   Large         1       1
11   Large  1      1

[HectorH]

I had a Eureka moment yesterday and answered my on question thanks for the help.

FYI Select sum(Mon) as Monday, sum (Tues).....
from ( Select avg(Read),0,0,0,0,0 from table where weekday(readdate,2)=1 group by size union Select 0, avg(Read),0,0.....)
group by Size