Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate

|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

AVG() aggregate with CASE

i have 2 columns of type decimal that are divided and rounded and
return zero if the divisor is zero. it all works fine until i try to
AVG() them. now it seems my CASE to avoid dividing by zero is no
longer working...or i'm just approaching it all the wrong way. my
brain is a bit fried from staying up late to meet a deadline and i was
wondering if someone could give some advice.
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
b.Value1 = 101664
a.Value2 = 7730
Rating = 1.31
i now try putting the AVG() function to work like this:
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
* 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
b.ID GROUP BY a.Value2
if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
get one record with the Rating = 1.07. yay!
the problem comes in where sometimes, the Values are 0. before using
the AVG() function, the CASE took care of these columns so that my
Rating results would be 0. but now i keep getting a "Divide by zero
error encountered". if the CASE is working, it shouldn't even try the
AVG() calculation right? or am i approaching this the wrong way? and
no, i can't use the SET ANSI_WARNINGS OFF because we're not using
stored procedures in this case.
thanks for any input or insight you may provide!
susieWhy not avoid the 0 problem by putting in a WHERE clause? ie
WHERE value1 > 0
AND value2 > 0
If that's not going to work, post some more sample values for tables a and b
.
Thanks
Damien
"TuBuGuRL" wrote:

> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>|||hi damien,
thanks for the reply. the WHERE clause is a good idea, but i don't
want to exclude these values, i need them to show up as averaged 0 in
my report.
i just realized that in my post above it's supposed to say "... ROUND(
(b.Value1 / a.Value2) ) ..." NOT "... ROUND( (b.Value1 / b.Value2) )
..."
anyway more sample data:
a.Value2 = 5910
b.Value1 = 120779
Rating = 2.04
5 different Ratings: 2.04, 1.01, 0.98, 0.91, 0.41
Avg() rating = 1.07
data used when i receive the error:
a.Value2 = 0
b.Value2 = 0
Rating = .0000000000000000
5 different Ratings: all .0000000000000000
shouldn't the CASE i use in the SELECT avoid even trying to AVG() the 0
Ratings?
thanks,
susie|||Put the CASE inside the AVG
...
AVG(CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND(...) END)
...
TuBuGuRL wrote:
> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>

AVG using ROW_NUMBER

I'm using SQL Server 2005, sp 2. My query is below. What I want to see for the results is the average of all of partition 1, the average of partition 2, etc.Does anybody know how I can get this?

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

Results:

Partition This Year Date Code Location Code

1 .85 20070101 1

2 .58 20070509 1

1 .52 20070808 2

2 .54 20070905 2

3 .26 20070104 3

3 .26 20070905 3

Is this what you're looking for?

Code Snippet

select

Partition

,(sum(Thisyear)/count(*)) 'ThisYearAvg'

from

(

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

) a

group by

Partition

|||

The average of what?

select

...,

avg(Score) over(partition by Shop.Location_Code) as avg_score

from

....

AMB

|||

Yes Anthony, That's exactly what I was looking for. Thank you so much for your help!

Lindsay

avg time difference

In my table i have two cols and datatype datetime, for example they has the values,
---StartDate ------------ StopDate --
______________________________________________
01/05/2005 7:30 AM -------- 03/05/2005 10:00 AM
13/05/2005 2:30 PM -------- 01/08/2005 8:00 PM
_____________________________________________
How do I find out the avg time difference between StopDate and StartDate? I triedAVG(StopDate - StartDate ),but it's giving me the following error message.
"The average aggregate operation cannot take a smalldatetime data type as an argument."
Thanks for any reply.

polash26 wrote:

I triedAVG(StopDate - StartDate ),but it's giving me error message. Thanks for any reply.

The key to solving any programming problem is to read the error messageand understand what it is telling you. When you seek help fromother programmers, the very first thing you are going to be asked is"what exactly does the error message say?"
In your case, I am assuming you are receiving this error message:

The average aggregate operation cannot take a datetime data type as an argument.

Try using the DATEDIFF function, which will return you an integer datatype. You CAN use the AVG function on an integer data type.
This, for example, will give you the average number of hours between the myDateTime column and the current system date:

SELECT AVG(DATEDIFF(hh,myDateTime, GETDATE())) FROM test

avg of timestamp

Is it possible to use AVG with timestamp?
I have a timestamp column with format 00:00:00, and I want to get the
average time of all rows for that column.
When I try it, I get this error:
The average aggregate operation cannot take a timestamp data type as an
argument.
Thanks!
A timestamp is a point in time. So averaging is impossible.
This means that you would have to define the averaging formula yourself.
Let's assume all your datetime columns have the date 1900-01-01. In that
case you might use the formula:
SELECT
DATEADD(second,AVG(DATEDIFF(second,'19000101',MyDa teColumn)),'19000101')
FROM MyTable
HTH,
Gert-Jan
chrismtoth@.gmail.com wrote:
> Is it possible to use AVG with timestamp?
> I have a timestamp column with format 00:00:00, and I want to get the
> average time of all rows for that column.
> When I try it, I get this error:
> The average aggregate operation cannot take a timestamp data type as an
> argument.
> Thanks!

avg of timestamp

Is it possible to use AVG with timestamp?
I have a timestamp column with format 00:00:00, and I want to get the
average time of all rows for that column.
When I try it, I get this error:
The average aggregate operation cannot take a timestamp data type as an
argument.
Thanks!A timestamp is a point in time. So averaging is impossible.
This means that you would have to define the averaging formula yourself.
Let's assume all your datetime columns have the date 1900-01-01. In that
case you might use the formula:
SELECT
DATEADD(second,AVG(DATEDIFF(second,'19000101',MyDateColumn)),'19000101')
FROM MyTable
HTH,
Gert-Jan
chrismtoth@.gmail.com wrote:
> Is it possible to use AVG with timestamp?
> I have a timestamp column with format 00:00:00, and I want to get the
> average time of all rows for that column.
> When I try it, I get this error:
> The average aggregate operation cannot take a timestamp data type as an
> argument.
> Thanks!

avg of timestamp

Is it possible to use AVG with timestamp?
I have a timestamp column with format 00:00:00, and I want to get the
average time of all rows for that column.
When I try it, I get this error:
The average aggregate operation cannot take a timestamp data type as an
argument.
Thanks!A timestamp is a point in time. So averaging is impossible.
This means that you would have to define the averaging formula yourself.
Let's assume all your datetime columns have the date 1900-01-01. In that
case you might use the formula:
SELECT
DATEADD(second,AVG(DATEDIFF(second,'1900
0101',MyDateColumn)),'19000101')
FROM MyTable
HTH,
Gert-Jan
chrismtoth@.gmail.com wrote:
> Is it possible to use AVG with timestamp?
> I have a timestamp column with format 00:00:00, and I want to get the
> average time of all rows for that column.
> When I try it, I get this error:
> The average aggregate operation cannot take a timestamp data type as an
> argument.
> Thanks!

avg of most current 50 only

I have a query that returns the averages for a selected group of records.
select AVG(h.stkhstClose), h.stkhstcsisym
from stkhst h
JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
and u.unvmemUnvID = 29001
group by h.stkhstcsisym
order by h.stkhstcsisym
this works and returns 99 averages. However, I need it to average only the
last 50 records of each group based on the date. I'm looking for a clean way
to do this without using a temporary table or cursor. and ideas would be
appreciated
thanks
kesJust a guess. See my signature for a more precise answer.
SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
<whatever> ORDER BY datecolumn DESC) x
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in message
news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> I have a query that returns the averages for a selected group of records.
> select AVG(h.stkhstClose), h.stkhstcsisym
> from stkhst h
> JOIN unvmem u on h.stkhstcsisym = u.unvmemCsiId
> and u.unvmemUnvID = 29001
> group by h.stkhstcsisym
> order by h.stkhstcsisym
> this works and returns 99 averages. However, I need it to average only the
> last 50 records of each group based on the date. I'm looking for a clean
way
> to do this without using a temporary table or cursor. and ideas would be
> appreciated
> thanks
> kes|||Thank You Aaron (you seem to answer a lot of my postings and your suggestion
s
have always proven helpful)
this will get the average for one group but how about the rest? Would a
where stkhstDate IN (select top 50 stkhstdate from stkhst where stkhstid =
xx order by stkhstdate DESC)
thank you
kes
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||>> this will get the average for one group but how about the rest?
Did you read Aaron's post? To repeat:
See his signature for a more precise answer.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
Anith|||ok, fair enough.
stkhst:
CREATE TABLE [stkhst] (
[stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
[stkhstCsiSym] [int] NULL ,
[stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstDate] [int] NULL ,
[stkhstOpen] [decimal](9, 4) NULL ,
[stkhstHi] [decimal](9, 4) NULL ,
[stkhstLow] [decimal](9, 4) NULL ,
[stkhstClose] [decimal](9, 4) NULL ,
[stkhstVol] [int] NULL ,
[stkhstDiv] [int] NULL ,
[stkhstX] [int] NULL ,
[stkhstO] [int] NULL ,
[stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
[stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stkhstCPosL] [int] NULL ,
[stkhstCPosH] [int] NULL ,
[stkHstCCol] [int] NULL
) ON [PRIMARY]
GO
unvmem:
CREATE TABLE [unvmem] (
[unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
[unvmemCsiId] [int] NOT NULL ,
[unvmemUnvID] [int] NOT NULL ,
[unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
) ON [PRIMARY]
GO
"Aaron [SQL Server MVP]" wrote:

> Just a guess. See my signature for a more precise answer.
> SELECT AVG(whatever) FROM (SELECT TOP 50 whatever FROM table WHERE
> <whatever> ORDER BY datecolumn DESC) x
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Kurt Schroeder" <KurtSchroeder@.discussions.microsoft.com> wrote in messag
e
> news:735FDCDB-B93C-45ED-94EA-0831429F5CB3@.microsoft.com...
> way
>
>|||noted, posted
thanks
kes
"Anith Sen" wrote:

> Did you read Aaron's post? To repeat:
> See his signature for a more precise answer.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> --
> Anith
>
>|||What about sample data and desired results?
The point is that we're not going to drive to Wichita or Kansas or wherever
you are to see what data is in your table and try to figure out what result
you want from that data. And we're certainly not going to spend our
afternoon inventing fictitious but possibly unrealistic data to populate
your empty table, then spend time developing a solution against that, only
to find out all the "buts" that come with the assumptions we made. Please
supply sample data in the form of INSERT statements, and the resultset you
want based on that data.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO|||Kurt,
Your tables seem to have no primary keys which is a critical design flaw.
Generally for such problems, others cannot test the solutions without sample
data. You have not provided that either. Also as a side note, if your scheme
allows, you may want to look closely at your naming convention as well.
Here is another attempt with guesswork:
SELECT AVG( stkhstClose ), stkhstcsisym
FROM ( SELECT TOP 50 h.stkhstClose, h.stkhstcsisym
FROM stkhst h
INNER JOIN unvmem u
ON h.stkhstcsisym = u.unvmemCsiId
WHERE u.unvmemUnvID = 29001
ORDER BY h.stkhstcsisym ) D ( stkhstClose, stkhstcsisym )
GROUP BY stkhstcsisym ;
Anith|||Kurt,
You want to extract and average the last 50 records for each group...
Just add a where clause that restricts the query to operate only on those
records which have 50 or less "partners" (in the same group) after them...
Select AVG(h.stkhstClose), h.stkhstcsisym
From stkhst h
Where (Select Count(*) From stkhst
Where stkhstcsisym = h.stkhstcsisym
And DateColumn >= h.DateColumn) <= 50
Select * From
"Kurt Schroeder" wrote:
> ok, fair enough.
> stkhst:
> CREATE TABLE [stkhst] (
> [stkhstID] [int] IDENTITY (1, 1) NOT NULL ,
> [stkhstCsiSym] [int] NULL ,
> [stkhstSym] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstDate] [int] NULL ,
> [stkhstOpen] [decimal](9, 4) NULL ,
> [stkhstHi] [decimal](9, 4) NULL ,
> [stkhstLow] [decimal](9, 4) NULL ,
> [stkhstClose] [decimal](9, 4) NULL ,
> [stkhstVol] [int] NULL ,
> [stkhstDiv] [int] NULL ,
> [stkhstX] [int] NULL ,
> [stkhstO] [int] NULL ,
> [stkhstXO] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstBuySell] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_stkhst_stkhstBuySell] DEFAULT ('U'),
> [stkhstLine] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [stkhstCPosL] [int] NULL ,
> [stkhstCPosH] [int] NULL ,
> [stkHstCCol] [int] NULL
> ) ON [PRIMARY]
> GO
> unvmem:
> CREATE TABLE [unvmem] (
> [unvmemRecID] [int] IDENTITY (1, 1) NOT NULL ,
> [unvmemCsiId] [int] NOT NULL ,
> [unvmemUnvID] [int] NOT NULL ,
> [unvmemActive] [bit] NULL CONSTRAINT [DF_unvmem_unvmemActive] DEFAULT (1)
> ) ON [PRIMARY]
> GO
> "Aaron [SQL Server MVP]" wrote:
>|||My apologies, I did not mean to imply that I needed more than advise. Please
understand that I do not feel it appropriate to ask for more than just that.
I feel it would be unfair to you or anyone else to do my work for me.
Aaron, your first posting to my question gave me what I needed to search for
the answer. My real query is much more complex, but this part of it was
simple enough to post for advice.
Again I wish to thank you for your help.
Humbly yours
kes
"Aaron [SQL Server MVP]" wrote:

> What about sample data and desired results?
> The point is that we're not going to drive to Wichita or Kansas or whereve
r
> you are to see what data is in your table and try to figure out what resul
t
> you want from that data. And we're certainly not going to spend our
> afternoon inventing fictitious but possibly unrealistic data to populate
> your empty table, then spend time developing a solution against that, only
> to find out all the "buts" that come with the assumptions we made. Please
> supply sample data in the form of INSERT statements, and the resultset you
> want based on that data.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
10
10/3 = 3.33 but the result is 3
SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
--
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:
> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG Measure

In AS2005 how i make a measure with tha aggregate function avg?

Thanks

Try using AverageOfChildren aggegate function and see if it works for you.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

i have tried the AverageOfChildre but i get an error because i'm using the standar edition...

I can't believe there is an easy way to get an avg measure :(

|||

One easy way I have found to get and average is to create a grain count measure and then sum up the measure you would like to average and then create a calculated member that takes the sum of the measure and then divides by the grain count.

HTH

Mark

http://mgarner.wordpress.com

|||

I create a measure SumNumber with the sum of the field, and another measure CountNumber with the count of the field. Then i create a calculated member like this:

In the expression i write: SumNumber/CountNumber and i get an error what i am doing wrong?

|||

I am new using AS2005, i don't know how to make a calculated member.. what i am doing is making a new named calculation... is the same thing?

|||

Javier,

To create a new calculated member, go to the calculations tab in your cube in BI Studio and then do one of two things, click on the "New Calculated Member" button on the tool bar. It looks like a calculator - or you can go to the menu and select "Cube", "New Calculated Member".

Once you have done that, you should put the name at the top in the box labeled "Name". (Average). Then in the "Expression" box, put the calculation. What you have looks correct, although it would probably throw an error if you divide by 0.

I would suggest getting your cube created w/o the calculated member and then pull both of your measures (count and sum) on to a pivot table and make sure they look ok and that the count won't be 0.

HTH

Mark

http://mgarner.wordpress.com

|||Thanks!!! now.. i can have a calculated member in a column of the drilldown?

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

AVG MDX Function

I have been trying to solve a calculated measure using both the sum/count methods and the avg function, but neither is working for the result that I want. We are trying to come up with an average course score based on an average test score. Example: user takes two tests (test1 twice for 50% and 100% - avg = 75% test2 once for 100%) and the course average would be (75+100)/2 = 87.5%. In our MDX we are getting (50+100+100)/3 = 83.33% instead.

We do have a hierarchy set based on course-->test-->test iteration where test iteration is each individual score. The overall average works the way you would typically thing with the sum/count method, but not based on how we want it to calculate AVG(AVG(test1 scores) + AVG(Test2 scorces)). I tried to use the AVG function, but this only works at the lowest level in the hierarchy and then appears to SUM up the results as you go up into the hierarchy (avg test1 75% test 2 100% course 175% -- not sure why the AVG works that way).

Can anyone tell me if this is even possible to get the average the way I am proposing or do I need to create some type of an aggregated take will calculations in the data warehouse to reference.

Thanks.

Seems like what you need to do is to define formula at the Test level to be Avg(Test.Children, Measures.CurrentMember), where at TestIteration level it will be Sum/Count formula. This will give you the results you are looking for.|||

I really appreciate your response and it is an honor to have you reply to this thread. I am sorry to sound stupid, but how do I define the calculations at the different levels and I am a bit confused with the Measures.CurrentMember. I guess I don't see how the testiteration level formula in the hierarchy gets passed to the other formula.

Thanks in advance.

|||

Here is a piece of MDX script which should do it:

CREATE AvgGrade = Measures.SumGrade / Measures.CountGrade;

(Test.Test.Test.MEMBERS, AvgGrade) = AVG(EXISTING Test.TestIteration.TestIteration.MEMBERS, AvgGrade);

|||

Thanks again for the quick response and I really appreciate you taking the time to assist me with this calculation. I just want to make sure I am doing this correctly. Here is what I put in the script editor:

CREATE AvgScore = Measures.[Test Scores] / Measures.[Completed Test Count];

([Fact Test Instance].[Hierarchy].[Course].[Test].Members, AvgScore) =

AVG(EXISTING [Fact Test Instance].[Hierarchy].[Course].[Test].[Iteration].Members, AvgScore)

We are currently using a hierarchy called [Hierarchy] (made up of Course-->Test-->Iteration) created off of a fact called [Fact Test Instance]. When I deploy this and drilldown to the test level I get a #VALUE!, so I am assuming I am doing something incorrectly. The other levels show the AvgScore result. Is there something wrong with my expression or does it have something to do with the solve order?

|||

The [Fact Test Instance].[Hierarchy].[Course].[Test] piece looks too long - there should be 3 parts - dimension, hierarchy, level and you have four parts. Is [Fact Test Instance] name of the dimension ? Then it should be

([Fact Test Instance].[Test].[Test].Members, AvgScore) = AVG(EXISTING [Fact Test Instance].[Iteration].[Iteration].Members, AvgScore);

Or another way to write it is

([Fact Test Instance].[Hierarchy].[Test].Members, AvgScore) = AVG([Fact Test Instance].[Hierarchy].CurrentMember.Children, AvgScore);

You will also need to do something similar about Course level too.

|||

Thanks again for your response and assistance. I modified the script to be the same as your second option and I am still getting the overall average at the test level when I browse the data within the BIDS browser using the AvgScore measure and the hierarchy called [Hierarchy] within the [Fact Test Instance] dimension. I have a student that has taken two different tests. Test1 taken twice - 33.33% and 100% for AVG 66.67% ; Test2 taken once - 90% for AVG 90% and the overall average would be (33.33+100+90)/3 = 74.44% and we would still like to get (66.67+90)/2 = 78.33%.

This definitely sounds like it should work, so is there something that I am missing or how can I validate that it is actually using this calculation at this specified level?

|||

From your description it sounds like you are looking above Test level, i.e. at Course level, since you want to see aggregate of two different tests. So you will also need

(AvgScore, [Fact Test Instance].[Hierarchy].Course.MEMBERS) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

|||

Once again I want to thank you so much for your assistance and you are absolutely right. I put in the additional script:

([Fact Test Instance].[Hierarchy].Course.MEMBERS, AvgScore) = AVG([Fact Test Instance].[Hierarchy].Children, AvgScore);

and I am now seeing the result that we are looking for. I am assuming that I simply have to repeat this up the hierarchy to keep this working this way. I really appreciate you taking the time to help me out with this issue.

AVG Function Overflow

Convert the column to BIGINT so you have more range: SELECT AVG(CONVERT(BIGINT, YourColumn)) FROM YourTable -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <Dennis Jackson@.discussions.microsoft.com> wrote in message news:43ea50a8-ae33-4816-bb70-8935293b923c@.discussions.microsoft.com...encountered on SQL 2005 CTP Sept.When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow. However, the STDEV function continues to work, which I *think* requires you to calculate average first...Any Suggestions on how to work around this?encountered on SQL 2005 CTP Sept.

When I am using the AVG function of 2 years worth of stock volume data(503 data points), I get a INT buffer overflow.

However, the STDEV function continues to work, which I *think* requires you to calculate average first...

Any Suggestions on how to work around this?|||The reason it doesn't work is AVG is computed as SUM/COUNT and not implemented natively. Since SUM returns for any expression of integer category except bigint, it will error out depending on the values. Ideally, we should implement AVG natively to handle the domain of values. For now, workaround is to cast the expression to higher precision data type.|||I already have the variable defined as a 4byte unsigned INT.
I may try to do AVG(column/1000000).. i will see if that will work.|||I assume then that STDEV is implemented as a native function then.
that was the part that wosnt making sense.. you need average to calculate STDEV, and that worked. but the logical intermediate item it couldnt calculate.

Also another wierd thing I ran across. when I tried to create a column that was stdevP(column) when the sql management studio parses it, it keeps converting it to COUNTBig(column) weird.

Do you know if that is a change they are planning to make?

AVG function on an integer column- truncation

When I use the AVG Function on an integer column, the result is truncated

Example:

Select AVG(field1) from table1

Field1 is an int field and has 4 rows with the values 114,115,115 and 115. This will return 114.

I can get the correct result by using the following SELECT:

SELECT CAST(AVG(CAST (field1 as decimal(18,1)))+ .5 as int) from table1

Am I missing something here? Is there an simpler way to do this?

Any help will be appreciated.

Steve D.

You are correct on the problem, but I am certain you can do it a little easier. Try this example:

DECLARE @.v_test TABLE ([num] INT)

INSERT INTO @.v_test VALUES(1)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

INSERT INTO @.v_test VALUES(2)

SELECT AVG(CONVERT(DECIMAL,num))

FROM @.v_Test

|||

The problem is that I need the resultant value to be an integer, not a decimal value. The query from above will return 1.75000.

I need the result back as an integer, which was why I had to cast it back to an integer after adding .5. Just looking for a simpler way to do this

|||

I'm not sure I understand. Would your answer be rounded to 2? Converting it to an INT is what it is doing for you.

You could use this to round it. If you want to recast it to INT, that is possible at this point.

SELECT ROUND(AVG(CONVERT(DECIMAL,num)),0)

|||

OR

SELECT CEILING(AVG(CONVERT(DECIMAL,num))) from yourTable

|||

More info.

I want the Average value to round to the nearest integer.

1.8 would round to 2

1.2 would round to 1

Ceiling looks like it would make 1.2 convert to 2.

Edit-

Tested this further- it looks like ROUND is what I need. It returns a decimal number (ex 1.0000), but I can convert that back to int.

AVG Function

I've imported several reports from Access that I am now trying to convert use
a shared SQL data source. Several of these report show an average value for a
datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
Does anyone have a workaround for RS to be able to average a time value on a
report. Thank youTry this:
=DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> I've imported several reports from Access that I am now trying to convert
use
> a shared SQL data source. Several of these report show an average value
for a
> datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> Does anyone have a workaround for RS to be able to average a time value on
a
> report. Thank you|||Thank you Robert,
This command returned an error "FromBinary is not a memvber of Date"
Any help is appreciated, thank you very much.
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > I've imported several reports from Access that I am now trying to convert
> use
> > a shared SQL data source. Several of these report show an average value
> for a
> > datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> >
> > Does anyone have a workaround for RS to be able to average a time value on
> a
> > report. Thank you
>
>|||Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
Try this for RS 2000:
=new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> Thank you Robert,
> This command returned an error "FromBinary is not a memvber of Date"
> Any help is appreciated, thank you very much.
>
>
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > I've imported several reports from Access that I am now trying to
convert
> > use
> > > a shared SQL data source. Several of these report show an average
value
> > for a
> > > datetime field in SQL, ex. a textbox with a value of
=AVG(Fields.T1.Value)
> > >
> > > Does anyone have a workaround for RS to be able to average a time
value on
> > a
> > > report. Thank you
> >
> >
> >|||Robert,
That was a huge help! Thank you very much. I am getting a #Error in some of
the footer fields using this calculation but I think this might be a data
issue.
Thank you!
"Robert Bruckner [MSFT]" wrote:
> Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> Try this for RS 2000:
> =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > Thank you Robert,
> >
> > This command returned an error "FromBinary is not a memvber of Date"
> >
> > Any help is appreciated, thank you very much.
> >
> >
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Try this:
> > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > I've imported several reports from Access that I am now trying to
> convert
> > > use
> > > > a shared SQL data source. Several of these report show an average
> value
> > > for a
> > > > datetime field in SQL, ex. a textbox with a value of
> =AVG(Fields.T1.Value)
> > > >
> > > > Does anyone have a workaround for RS to be able to average a time
> value on
> > > a
> > > > report. Thank you
> > >
> > >
> > >
>
>|||Another similar situation which help would be appreciated for.
I have a table with 4 datetime fields, storing only the time. In the report
I need to produce an average of these 4 fields like, (T1.Value + T2.Value +
T3.Value + T4.Value)/4. This obviously throws a similar error.
Any help is appreciated.
"halej51" wrote:
> Robert,
> That was a huge help! Thank you very much. I am getting a #Error in some of
> the footer fields using this calculation but I think this might be a data
> issue.
> Thank you!
>
> "Robert Bruckner [MSFT]" wrote:
> > Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> > Try this for RS 2000:
> > =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > > Thank you Robert,
> > >
> > > This command returned an error "FromBinary is not a memvber of Date"
> > >
> > > Any help is appreciated, thank you very much.
> > >
> > >
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > Try this:
> > > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > > I've imported several reports from Access that I am now trying to
> > convert
> > > > use
> > > > > a shared SQL data source. Several of these report show an average
> > value
> > > > for a
> > > > > datetime field in SQL, ex. a textbox with a value of
> > =AVG(Fields.T1.Value)
> > > > >
> > > > > Does anyone have a workaround for RS to be able to average a time
> > value on
> > > > a
> > > > > report. Thank you
> > > >
> > > >
> > > >
> >
> >
> >

Avg function

The following function returns an average in minutes between to dates.
=Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
Fields!TODO_ACTL_END_DT.Value ))/60
My problem is in figuring out how to truncate decimal places for example the
report returns 36.2795358649783 when all I want is 36.
If any one has any ideas that would be greatly appreciated.
--
kmatth007use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60,"0")
"kmatth007" wrote:
> The following function returns an average in minutes between to dates.
> =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60
> My problem is in figuring out how to truncate decimal places for example the
> report returns 36.2795358649783 when all I want is 36.
> If any one has any ideas that would be greatly appreciated.
> --
> kmatth007|||Thanks! This worked.
kmatth007
"ש×?×?×?" wrote:
> use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60,"0")
> "kmatth007" wrote:
> > The following function returns an average in minutes between to dates.
> >
> > =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60
> >
> > My problem is in figuring out how to truncate decimal places for example the
> > report returns 36.2795358649783 when all I want is 36.
> >
> > If any one has any ideas that would be greatly appreciated.
> >
> > --
> > kmatth007