HELP PLEASE! - select with AVG function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

HELP PLEASE! - select with AVG function

Author

Topic

strippy
Starting Member

8 Posts

Posted - 2005-02-23 : 08:21:37

hi all...

i'm working with sql server 2000, and this is my problem.

i have one table that consists of two columns. the first one is "state" (type - numeric)" and the second one is date" (type - datetime). it has just this values:
date time: dd-mm-yyyy

state__________ date
110__________2.2.2005
150__________3.2.2005
120__________4.2.2005
160__________17.2.2005
140__________24.2.2005
130__________3.3.2005
140__________4.3.2005
150__________8.3.2005
130__________12.3.2005
160__________18.3.2005
170__________27.3.2005

now i need to calculate an AVG of state between lets say 3.2.2005 and 15.3.2005.......
but my problem lies here that i have to calculate all days in this date range....and the values of the days that r not here r for example:
4.2.2005 value for state is 120
for all days from 5.2.2005 till 16.2.2005 the value of state should be the one from 4.2.2005 (as it was the last day where there was a change)........then the same story for other days which r not dislayed in the table.......

i cant create another table not even a temporary one.

i hope u can help me.....

and thanx also for any help or hints u could give me!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 08:56:34

you will need an extra table with all dates you must join to...

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-23 : 09:06:09

Why couldn't it be done with a subquery instead of a temp table?

I'd give it a try, but I don't understand the problem. Looking for "Average Day"? for each state? For all days between start date and finish date?

Help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 09:07:59

well you could do something like:


set dateformat dmy
declare @MyTable table (state int, date datetime)
insert into @MyTable (state, date)
select 110, '2.2.2005' union all
select 150, '3.2.2005' union all
select 120, '4.2.2005' union all
select 160, '17.2.2005' union all
select 140, '24.2.2005' union all
select 130, '3.3.2005' union all
select 140, '4.3.2005' union all
select 150, '8.3.2005' union all
select 130, '12.3.2005' union all
select 160, '18.3.2005' union all
select 170, '27.3.2005'

select	dayDiffToNextDate*state, dayDiffToNextDate, state, date
from 	(
	select 	isnull(datediff(d, date, (select top 1 date from @MyTable where date>t1.date order by date)), 0) as dayDiffToNextDate, state, date
	from 	@MyTable t1
	) t2
where date between '6.2.2005' and '15.3.2005' 

select	avg(dayDiffToNextDate*state)
from 	(
	select 	isnull(datediff(d, date, (select top 1 date from @MyTable where date>t1.date order by date)), 0) as dayDiffToNextDate, state, date
	from 	@MyTable t1
	) t2
where date between '6.2.2005' and '15.3.2005'

it depends how you want to handle the start and end date that are not in table.

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-23 : 09:16:26

I still don't understand the problem. Can you state it so it'll meet my reading level? (4th grade).

strippy
Starting Member

8 Posts

Posted - 2005-02-23 : 09:17:21

thanx for your answers.i see that i havent explained all.....sorry for that.

ok well first the table is about a bank account. "state" (i forgot an exact english word for this) of an account on a particular date.....like on 04.02.2005 i had 120$ on my account.
my problem lies here that i have to calculate an average value of account (AVG(state)) for all days in a given date range. and i only have the values for days on which there was a transaction on that account. and for all other days i have to put the value of "state" before those days that r not mentioned in the table.

i cant create another table with dates and also with transactions, cause my task is to try to calculate an average value of state of an account in a given date range based on the values in the table.

i hope that this clears some things up.....

and thanx for the help

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 09:20:01

if he has missing dates
ie: 13 days between 4.2.2005 and 17.2.2005
then he wants to use the state value on 4.2.2005 for those 12 days between also.
that's how i got it.

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-23 : 09:21:55

Cryptic Answer: Tally Ho!

I suspect this will be part of the solution...

strippy
Starting Member

8 Posts

Posted - 2005-02-23 : 09:23:05

yep thats it........thatnx spirit

just i dont know how to write the procedure (i think it should be a procedure) which would recognize the dates that r missing, put the value of ie. 4.2.2005 for those 12 days that r missing and then based on all the values to calculate avg of the state in that date range.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 09:24:50

well i gave you the code that does something similar to what you want. do you understand the logic i used in it?

Go with the flow & have fun! Else fight the flow

strippy
Starting Member

8 Posts

Posted - 2005-02-23 : 09:31:17

i'll try it.if it doesent work will say what didnt work.....

in esence its this kind of select:
select AVG(state) "Average state of account between 3.2.-15.3.2005"
from mytable
where date between '3.2.2005' and '15.3.2005'

but this select calculates only the values in the table........i need it to calculate into average all days in that date range.......

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-23 : 09:36:32

that's why i calculate the diffference in dayDiffToNextDate. it's the differnece between the date in the "current" row and the first next date when ordered on date.
so by doing dayDiffToNextDate*state you get the sum of states for all missing days plus current day.

Go with the flow & have fun! Else fight the flow

strippy
Starting Member

8 Posts

Posted - 2005-02-23 : 09:42:08

i understand now....thank u very much....

i'm trying it out right now.

strippy
Starting Member

8 Posts

Posted - 2005-02-24 : 02:32:04

thank u very much for the script.........just there is only one small detail: when it calculates the average of state field, it derives the cumulate value with 11 and not with 48 (as from 06.02. till 15.03 there r 48 days).......so in the en it looks like there were 11 days only and the average value off sate is 525 which is too high concidering that values do not top 190.

so is there a way that it derives the sum of values with the total number of days in a date range?

thanx again for the help!!!

strippy
Starting Member

8 Posts

Posted - 2005-02-25 : 04:19:13

thanx for all the help........

ok well this is a solution that i did with my boss.....

declare @startdate datetime
declare @enddate datetime
declare @total numeric (19,2)
declare @state numeric (19,2)
declare @date datetime
set @startdate = 'feb 5, 2005'
set @enddate = 'mar 15, 2005'
set @total = 0
set @date = @startdate
while @date <= @enddate
begin
select @state = state from mytable
where date = (
select max(date) from mytable where date <= @date)
set @total = @total + @state
set @date = @date + 1
end
/*print @total*/
print @total/datediff(day, @startdate, @enddate)+1

thanx again for all the help!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-25 : 08:41:26

ok using a loop is also an option.
i didn't even think of that... have to go on an de-"set based thinking" course

Sretno!

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-25 : 09:16:56

Someone please post a set-based solution. Loops hurt my head!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-25 : 09:51:22

well since you asked so nicely, Sam


select avg(final)
from
(
select	(dayDiffToNextDate*state)/cast(sum(dayDiffToNextDate) as decimal(12,6))as final
from 	(
	select 	isnull(datediff(d, date, (select top 1 date from @MyTable where date>t1.date order by date)), 0) as dayDiffToNextDate, state, date
	from 	@MyTable t1
	) t2
where date between '5.2.2005' and '15.3.2005' 
group by dayDiffToNextDate, state
) t

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-02-25 : 10:08:20

Good job. I hate to leave a thread without a set based solution.

I've gotta go now, or I'd try cooking up a solution with a Tally table.

Something involving

SELECT DATEADD(dd, ID, @StartDate)
FROM Tally
WHERE ID BETWEEN 0 AND DATEDIFF(dd, @Startdate, @Enddate)

Adios!

strippy
Starting Member

8 Posts

Posted - 2005-02-28 : 02:35:52

thank u for the set based solution.....i hvala spirit...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources